Entity Framework and large queries. What's practical?

Question

I'm from old school where DB had all data access encapsulated into views, procedures, etc. Now I'm forcing myself into using LINQ for most of the obvious queries.

What I'm wondering though, is when to stop and what practical? Today I needed to run query like this:

SELECT D.DeviceKey, D.DeviceId, DR.DriverId, TR.TruckId, LP.Description
FROM dbo.MBLDevice D
LEFT OUTER JOIN dbo.DSPDriver DR ON D.DeviceKey = DR.DeviceKey
LEFT OUTER JOIN dbo.DSPTruck TR ON D.DeviceKey = TR.DeviceKey
LEFT OUTER JOIN 
    (
    SELECT LastPositions.DeviceKey, P.Description, P.Latitude, P.Longitude, P.Speed, P.DeviceTime 
    FROM dbo.MBLPosition P
    INNER JOIN 
    (
        SELECT D.DeviceKey, MAX(P.PositionKey) LastPositionKey 
        FROM dbo.MBLPosition P
        INNER JOIN dbo.MBLDevice D ON P.DeviceKey = D.DeviceKey
        GROUP BY D.DeviceKey
    ) LastPositions ON P.PositionKey = LastPositions.LastPositionKey 
    ) LP ON D.DeviceKey = LP.DeviceKey
WHERE D.IsActive = 1

Personally, I'm not able to write corresponing LINQ. So, I found tool online and got back 2 page long LINQ. It works properly-I can see it in profiler but it's not maintainable IMO. Another problem is that I'm doing projection and getting Anonymous object back. Or, I can manually create class and project into that custom class.

At this point I wonder if it is better to create View on SQL Server and add it to my model? It will break my "all SQL on cliens side" mantra but will be easier to read and maintain. No?

I wonder where you stop with T-SQL vs LINQ ?

EDIT

• Model description.
• I have DSPTrucks, DSPDrivers and MBLDevices.
• Device can be attached to Truck or to Driver or to both.
• I also have MBLPositions which is basically pings from device (timestamp and GPS position)

What this query does - in one shot it returns all device-truck-driver information so I know what this device attached to and it also get's me last GPS position for those devices. Response may look like so:

There is some redundant stuff but it's OK. I need to get it in one query.

Answer 1

In general, I would also default to LINQ for most simple queries.

However, when you get at a point where the corresponding LINQ query becomes harder to write and maintain, then what's the point really? So I would simply leave that query in place. It works, after all. To make it easier to use it's pretty straight-forward to map a view or cough stored procedure in your EF model. Nothing wrong with that, really (IMO).

Answer 2

I just happened to be looking at the concept for something I'm working on. In the OP, the author provides the query that is being used to pull the data. IMHO, using what already works is always best.

Using the current version of the Entity Framework (I'm currently using EF6), you can instantiate the model using the exact query that is in the DB code, as a .sql file embedded in the project (Web app or WebAPI), based on this solution.

The Code the for the Context winds up looking like this:

public partial class CustomObjectDataContext : DbContext
{
    SqlConnection SqlConn { get; set; }
    SqlCommand CustomObjectQuery { get; set; }
    public string ConnString { get; private set; }

    public DbSet<CustomObject> CustomObjectList { get; set; }

    public CustomObjectDataContext(string connString)
    {
        ConnString = connString;
        SqlConn = new SqlConnection(ConnString);

        #region External file as Resource (preferred)
        string customSQLQuery = AppContext.BaseDirectory + @"path to .sql file";
        string query = File.ReadAllText(customSQLQuery);  //it is possible to place the query in-line, but it is not recommended
        #endregion

        
        CustomObjectQuery = new SqlCommand(query);
    }
    public CustomObjectDataContext(DbContextOptions<FacetDataContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<CustomObject>(co =>
        {
            co.HasNoKey();
            co.ToSqlQuery(CustomObjectQuery.CommandText);
        });
    }


    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlServer(ConnString);
        base.OnConfiguring(options);
    }

}

I use this as part of a reporting solution that has three different DbContexts, and outputs the resulting data to a spreadsheet. The process is quick, effective, and maintainable.

Answer 3

You can firstly store Linq queries in variables which may help to make it not only more readable, but also reusable.

An example maybe like the following:

        var redCars = from c in cars
                      where c.Colour == "red"
                      select c;

        var redSportsCars = from c in redCars
                            where c.Type == "Sports"
                            select c;

Queries are lazily executed and not composed until you compile them or iterate over them so you'll notice in profiler that this does produce an effecient query

You will also benifit from defining relationships in the model and using navigation properties, rather than using the linq join syntax. This (again) will make these relationships reusable between queries, and more readable (because you don't specify the relationships in the query like the SQL above)

Generally speaking your LINQ query will be shorter than the equivalent SQL, but I'd suggest trying to work it out by hand rather than using a conversion tool.

With the exception of CTEs (which I'm fairly sure you can't do in LINQ) I would write all queries in LINQ these days

Answer 4

I find when using LINQ its best to ignore whatever sql it generates as long as its retrieving the right thing and is performant, only when one of those doesn't work do I actually look at what its generating.

In terms of the sql it generates being maintainable, you shouldn't really worry about the SQL being maintainable but more the LINQ query that is generating the SQL.

In the end if the sql is not quite right I believe there are various things you can do to make LINQ generate SQL more along the lines you want..to some extent.

AFAIK there isn't any inherent problem with getting anonymous objects back, however if you are doing it it multiple places you may want to create a class to keep things neater.