CODEWITHSUNDEEP
c#linq
Terrance Jackson
Terrance Jackson

Reputation: 1069

Linq to Entities Selecting Data with Max Date?

I am having issue selecting maxDate within also selecting other columns within a query

This is the query that I am trying to convert to linq to entities. The tableexpression would return a list of ids along with the corresponding max insertdates

    WITH TableExpression
AS
(
SELECT   Id, Max(InsertDate) as MaxInsertDate
FROM     MyTable
WHERE    UploadDate BETWEEN '10/1/2018' AND '12/12/2018'
GROUP BY Id
)

SELECT  * 
FROM    MyTable t Join TableExpression on t.Id = TableExpression.Id
WHERE   t.InsertDate = TableExpression.MaxInsertDate    
        AND UploadDate BETWEEN '10/1/2018' and '12/12/2018'

EDIT UPDATE: Thanks to the answer below this query gets me what I need if I do a .ToList(); However I need to include this query as a join

  var maxDateQuery = ctx.MyEntity
        .Where(m => m.InsertDate >= fromDate && m.InsertDate <= toDate)
        .ToArray()
        .GroupBy(x => x.Id)
        .Select(g => new { Id = g.Key, MaxInsertDate = g.Max(y => y.InsertDate) }).AsQueryable();

The second issue is the join clause

var mainQuery = ctx.MyTable
        .Where(m => m.InsertDate >= fromDate && m.InsertDate <= toDate).AsQueryable();



 var joinedQuery = maxDateQuery.Join(mainQuery, max => max.Id, main => main.Id, (mdq, mq) => new { maxRecords = mdq, mainRecords = mq })
                                                  .Select(joined => new
                                                  {
                                                      main = joined.mainRecords,
                                                      max = joined.maxRecords
                                                  }).ToList();

I am getting a crazy error once I hit the join statement: "This method supports the LINQ to Entities infrastructure and is not intended to be used directly from your code."

Upvotes: 1

Views: 299

Answers (1)

mm8
mm8

Reputation: 169210

You could group the in-memory results after you have selected the records from the database, e.g.:

var maxDateQuery = ctx.MyTables
        .Where(m => m.InsertDate >= fromDate && m.InsertDate <= toDate)
        .ToArray()
        .GroupBy(x => x.Id)
        .Select(g => new { Id = g.Key, MaxInsertDate = g.Max(y => y.InsertDate)) }};

This should give you an IEnumerable<a'> where a' is an anonymous type with an Id and a MaxInsertDate property if that's what you want.

Trying to convert fairly complex SQL queries to LINQ-To-Entities is pointless. You should either group the in-memory results like above or use raw SQL queries

Upvotes: 3

Related Questions