CODEWITHSUNDEEP
sqllinqlinq-to-sql
user2137354
user2137354

Reputation:

LINQ to SQL with JOIN, GROUP BY, HAVING and MAX

I am trying to translate this SQL query into LINQ:

SELECT
        T1.FileID,
        MAX(T1.Date) AS MaxDate
FROM    
        T1
        JOIN
        T2 ON T1.Code = T2.Code
WHERE
        T2.Category = 'C100'
GROUP BY
        T1.FileID
HAVING
        T1.FileID LIKE 'F01%'

I am getting all the dates instead of just the Max of each Date per FileID using this:

var query =

    from f in db.T1
    join c in db.T2 on f.Code equals c.Code
    group f by new { f.FileID, f.Date, c.Category } into g
    where g.Key.FileID.StartsWith("F01") && g.Key.Category.Equals("C100")
    select new
    {
        g.Key.FileID,
        MaxDate = g.Max(d=>d.Date)
    }

This seems more complicated than needed. How can I fix this?

Upvotes: 1

Views: 4230

Answers (2)

MarcinJuraszek
MarcinJuraszek

Reputation: 125650

Why does your SQL contain only one column in GROUP BY clause and LINQ query has three columns instead?

You should also move where statement before group by in your LINQ query.

var query =
    from f in db.T1
    join c in db.T2 on f.Code = c.Code
    where c.Category = "C100"
    group f by f.FileID into g
    where SqlMethods.Like(g.Key, "F01%")
    select new
    {
        FileID = g.Key,
        MaxDate = g.Max(d => d.Date)
    }

Upvotes: 3

Chandan Kumar
Chandan Kumar

Reputation: 4638

modify your linq with this :

while writing linq query you need to remember the order you define the formats

var query = ( from f in db.T1
              join c in db.T2 on f.Code = c.Code
              where c.Category = "C100"
              group f by  f.FileID into g
              select new
              {
                   FileID = g.Key,
                   MaxDate = g.Max(d=>d.Date)
              });

Upvotes: 2

Related Questions