CODEWITHSUNDEEP
.netsqllinqentity-framework-4
jrowe88
jrowe88

Reputation: 21

Efficient SQL from LINQ statement with joins and aggregate?

I am trying to do what is a relatively basic SQL statement to join and group some tables and summarize with aggregate functions. I would write it in SQL like this:

select
  p.LocationID,
  NumReadings = count(*),
  MinDate = min(t.[DateTime]),
  MaxDate = max(t.[DateTime])
from Station p inner join Data pd on p.LocationID = pd.ReadingLocationID
    inner join ApplicationDateTime t on t.ApplicationDateTimeID = pd.DateTimeID
group by p.LocationID

When I use the Linq statement below in EF4, it creates some hideous SQL (see very bottom). Any better ways to do this? Explicitly doing the joins instead of using the EF navigation properties makes it even worse.

I don't care about the aesthetics, but looking at the query execution, it takes 3-4x as long to execute the poorly formed SQL.

from s in Station
select new DataSummary
{
   ReadingLocationID = s.ReadingLocationID,
   StationIdentifier = s.StationIdentifier,
   NumReadings = s.Data.Count(),
   MinDateLoaded = s.Data.Min(d => d.ApplicationDateTime.DateTime),
   MaxDateLoaded = s.Data.Max(d => d.ApplicationDateTime.DateTime)
};

Here is the SQL (note: there are some additional complexities here, like a inherited relationship represented as another join, but that just causes another nesting level).

SELECT 
  [Project3].[LocationTypeID] AS [LocationTypeID], 
  [Project3].[ReadingLocationID] AS [ReadingLocationID], 
  [Project3].[LocationIdentifier] AS [LocationIdentifier], 
  [Project3].[C1] AS [C1], 
  CAST( [Project3].[C2] AS datetime2) AS [C2], 
  CAST( [Project3].[C3] AS datetime2) AS [C3]
FROM ( SELECT 
        [Project2].[ReadingLocationID] AS [ReadingLocationID], 
        [Project2].[LocationTypeID] AS [LocationTypeID], 
        [Project2].[LocationIdentifier] AS [LocationIdentifier], 
        [Project2].[C1] AS [C1], 
        [Project2].[C2] AS [C2], 
        (SELECT 
           MAX([Extent7].[DateTime]) AS [A1]
           FROM  [dbo].[Data] AS [Extent6]
           INNER JOIN [dbo].[ApplicationDateTime] AS [Extent7] ON [Extent6].[DateTimeID] = [Extent7].[ApplicationDateTimeID]
           WHERE [Project2].[ReadingLocationID] = [Extent6].[ReadingLocationID]) AS [C3]
            FROM ( SELECT 
            [Project1].[ReadingLocationID] AS [ReadingLocationID], 
            [Project1].[LocationTypeID] AS [LocationTypeID], 
            [Project1].[LocationIdentifier] AS [LocationIdentifier], 
            [Project1].[C1] AS [C1], 
            (SELECT 
               MIN([Extent5].[DateTime]) AS [A1]
               FROM  [dbo].[Data] AS [Extent4]
               INNER JOIN [dbo].[ApplicationDateTime] AS [Extent5] ON [Extent4].[DateTimeID] = [Extent5].[ApplicationDateTimeID]
               WHERE [Project1].[ReadingLocationID] = [Extent4].[ReadingLocationID]) AS [C2]
               FROM ( SELECT 
                  [Extent1].[ReadingLocationID] AS [ReadingLocationID], 
                  [Extent1].[LocationTypeID] AS [LocationTypeID], 
                  [Extent1].[LocationIdentifier] AS [LocationIdentifier], 
                  (SELECT 
                        COUNT(1) AS [A1]
                        FROM [dbo].[Data] AS [Extent3]
                        WHERE [Extent1].[ReadingLocationID] = [Extent3].[ReadingLocationID]) AS [C1]
                   FROM  [dbo].[ReadingLocation] AS [Extent1]
                   INNER JOIN [dbo].[Station] AS [Extent2] ON [Extent1].[ReadingLocationID] = [Extent2].[LocationID]
                   WHERE ([Extent1].[LocationTypeID] =  CAST( '1' AS int)) AND ([Extent2].[LineID] = 'ACBB3FDF-116C-4E8E-AA80-B925E4922AC8')
                   )  AS [Project1]
               )  AS [Project2]
)

Help! thanks.

Upvotes: 2

Views: 212

Answers (2)

Jacco
Jacco

Reputation: 3272

LINQ can do a lot, but it definitely ain't magic. Try to write your LINQ a bit more like you wrote your SQL. Define your joins.

This is what your LINQ-statement should look like:

Station
 .Join(Data, s => new { s.LocationID }, d => new { LocationID = d.ReadingLocationID }, (s,d) => new { s.ID, s.LocationID, d.DateTimeID })
 .Join(ApplicationDateTime, j1 => new { j1.DateTimeID }, t => new { DateTimeID = t.ApplicationDateTimeID }, (j1,t) => { j.ID, j.LocationID, t.DateTime })
 .Group(j2 => new { j2.ID, j2.LocationID })
 .Select(g => new DataSummary
   {
      StationIdentifier = g.Key.ID,
      ReadingLocationID = g.Key.LocationID,
      NumReadings = g.Count(),
      MinDateLoaded = g.Min(j2 => j2.DateTime),
      MaxDateLoaded = g.Max(j2 => j2.DateTime)
   });

NB: The code provided above is not tested!

Remember when creating a join in LINQ, the properties of the anonymous objects you use in your join should have the same names AND types. This took me some time to get my first join working. Example: suppose the DateTimeID column on your Data table can be NULL and the column ApplicationDateTimeID on ApplicationDateTime can not be NULL. You should then change that join to:

.Join(ApplicationDateTime, j1 => new { j1.DateTimeID }, t => new { DateTimeID = (int?)t.ApplicationDateTimeID }, (j1,t) => { j.ID, j.LocationID, t.DateTime })

Upvotes: 0

taylonr
taylonr

Reputation: 10790

How about using a different ORM? Specifically a MicroOrm such as PetaPoco, or Massive will let you write queries in SQL and get back .NET objects.

Both are Nuget Packages: PetaPoco, Massive so you can easily install them.

If you're comfortable writing SQL and would prefer to have control over your queries, they might be viable candidates.

Upvotes: 1

Related Questions