Convert SQL statement to Linq / Lambda Query

Question

Im using MEF and executing a task which needs to be grouped with aggregate functions only when it returns more than 1 record. I need both the Max of the start hour and the min of the end hour grouped into a single record like my sql would result in on the restuled task

var ohs = await Bl.UoW.Repositories.OperatingHours
    .FindInDataSourceAsync(oh => ((oh.ProductId == productTypeId 
        && oh.StateId == state) 
        || (oh.StateId == complianceHours.State)));

Here is the SQL that gets me basiccally what I need when more than 1 record returned

SELECT 
    StateId,
    MAX(ComplianceHourStart),
    MIN(ComplianceHourEnd)
FROM 
    OperatingHours 
GROUP BY
    StateId
HAVING 
    StateId = 'CA'

So when more than 1 I can filter it further but not sure how to achieve max and min?

if (ohs != null && ohs.Count() > 1)
{
    //
    ohs = ohs.GroupBy(x => x.State).Max(x => x.ComplianceHourStart?...

}

Thanks

NinjaNye · Accepted Answer · 2013-09-30 06:22:37Z

From your SQL, this should be close:

var result = context.OperatingHours
                    .GroupBy(oh => oh.StateId)
                    .Select(oh => new {StateId = oh.Key, 
                                       MaxStart = oh.Max(x => x.ComplianceHourStart),
                                       MinEnd = oh.Min(x => x.ComplianceHourEnd)});

...although I'm not sure why you are grouping when you are restricting the state id column (group key). The following should also suffice:

var result = context.OperatingHours
                    .Where(oh => oh.StateId == 'CA')
                    .Select(oh => new {MaxStart = oh.Max(x => x.ComplianceHourStart),
                                       MinEnd = oh.Min(x => x.ComplianceHourEnd)});

Dave Bish · Accepted Answer · 2013-09-24 23:12:56Z

1

Something like this should do it:

ohs = ohs.GroupBy(x => x.State)
    .Select(g => new 
    { 
        //You need to make a choice on StateId, here... First one?
        StateId = g.First().StateId, 
        MaxComplianceHourStart = g.Max(o => o.ComplianceHourStart),
        MinComplianceHourEnd = g.Min(o => o.ComplianceHourEnd) 
    });

answered Sep 24, 2013 at 23:12

Dave Bish

19.7k7 gold badges50 silver badges63 bronze badges

Collectives™ on Stack Overflow

Convert SQL statement to Linq / Lambda Query

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related