CODEWITHSUNDEEP
c#sqllinqlambda
Ian Campelo
Ian Campelo

Reputation: 163

Using Max, Group By, Join and Where in Lambda Expression

I'm try to make a complex query (instead to me), with Lambda expression. I have the SQL that I want to "translate" to Lambda.

SELECT MAX((SUBSTRING(tbp.dt,4,4)+SUBSTRING(tbp.dt,2,2)+SUBSTRING(tbp.dt,1,2))) as Dt, 
tb._n, tbp.number, tbp.dsc
FROM TB_A tb
JOIN TB_B_C tbp ON tbp.number = tb.number
WHERE tbp.rec = 0 AND tbp.processing = 0 AND tb._n != '' AND tbp.error = 0
GROUP BY tb._n, tbp.number, tbp.dsc

Until now I have this Lambda Expression:

var results = db.a
           .Join(db.b_c, proc => proc.number, andam => andam.number, (proc, andam) => new { proc, andam })
           .Where(d => d.proc._n != "" && d.andam.rec == false && d.andam.processing == false && d.andam.error)
           .ToList();

How can I finish my select to have the same result as the SQL query? If possible can you explain how to think properly when "translating" a query to Lambda?

Thanks a lot.

Upvotes: 0

Views: 2262

Answers (2)

Maciej Los
Maciej Los

Reputation: 8591

All what you need to do is

1) to add GroupBy and Select statements

or

2) to replace Join with GroupJoin.

Below examples are not related to your database schema...

Option 1) 

var results = ...
           .GroupBy(x=> new {x.Field1, x.Field2, x.Field3})
           .Select(grp=>new
            {
               Key = grp.Key,
               MaxVal = grp.Max(o=>o.Field1)
            });

Option 2) 

var result = db_a.Where(x=>x.Field1==1 && x.Field2==0)
            .GroupJoin(db_b.Where(x=>x.Field3==5),
                       a => a.PrimaryKey,
                       b => b.ForeignKey,
                       (a, b) => new
                           {
                               PK=a.PrimaryKey,
                               MaxVal=b.Max(o=>o.Field2)
                            });

source: https://msdn.microsoft.com/en-us/library/bb534297%28v=vs.110%29.aspx

Upvotes: 0

juharr
juharr

Reputation: 32266

It's usually easier to write with query syntax

var results = from tb in db.a
              join tbp in db.b_c on tb.number equals tbp.number
              where tbp.rec == 0 
                    && tbp.processing == 0 
                    && tb._n != string.Empty 
                    && tbp.error == 0
              group new {tb, tbp}  by new {tb._n, tbp.number, tbp.dsc} into grp
              select new
              {
                  grp.Key._n,
                  grp.Key.number,
                  grp.Key.dsc,
                  Dt = grp.Max(x => x.tbp.dt.Substring(4,4) 
                                  + x.tbp.dt.Substring(2,2) 
                                  + x.tbp.dt.Substring(0,2))
              };

Upvotes: 1

Related Questions