CODEWITHSUNDEEP
c#sqlentity-frameworklinq
Anup
Anup

Reputation: 9738

Linq Min in Select new & Multiple GroupBy columns

I want to write following query in Linq

INSERT INTO INOUTNEW (CODE,INDATE,TIME_DATE1,INOUTFLAG,TIME_FLD1,TIME_FLD2,TIME_FLD3) 
SELECT CODE,MIN(INDATE),TIME_DATE1,'I',TIME_FLD1,TIME_FLD2,'31/05/2015' FROM INOUT 
WHERE TIME_FLD1='T0003' AND INDATE >= '31/05/2015' AND INDATE <= '31/05/2015' 
AND TIME_DATE1='31/05/2015' 
GROUP BY CODE,TIME_DATE1,TIME_FLD1,TIME_FLD2

SO I am trying this :-

var data = ctx.tblInOut.Where(m => m.CompanyId == companyId && m.Time_Field1 == item.ShiftCode && m.InDate == StrInStart && m.InDate <= StrInEnd && m.Time_Date1 == InputDate).Select(m =>
                            new
                            {
                                EmployeeId = m.EmployeeId,
                                InDate = Min(m.InDate),
                                Time_Date1 = m.Time_Date1,
                                InOutFlag = m.InOutFlag    
                            }).ToList();

I am stuck in Min Part. How to get Min in Select? And How to add multiple GroupBy in Linq?

Upvotes: 2

Views: 1524

Answers (1)

Rob
Rob

Reputation: 27357

Try something like this:

var data = ctx.tblInOut
.Where(m => 
    m.CompanyId == companyId && 
    m.Time_Field1 == item.ShiftCode && 
    m.InDate == StrInStart && 
    m.InDate <= StrInEnd && 
    m.Time_Date1 == InputDate
)
.GroupBy(m =>
    new {
        m.Code,
        m.Time_Date1,
        m.Time_FLD1,
        m.Time_FLD2
})
.Select(g =>
new
{
    m.Key.Code,
    InDate = m.Min(gg => gg.InDate),
    m.Key.Time_Date1,
    Something = "I",
    m.Key.Time_FLD1,
    m.Key.Time_FLD2,
    SomeDate = "31/05/2015"
}).ToList();

To get Min, you must group first - otherwise it's trying to call Min on a single element.

.Key simply references the key of the group (in this case, a tuple of Code, Date1, Time_FLD1, Time_FLD2)

Upvotes: 5

Related Questions