Lightswitch 2013 Linq queries to Get min value

Question

I'm writing a timesheet application (Silverlight) and I'm completely stuck on getting linq queries working. I'm netw to linq and I just read, and did many examples from, a Linq book, including Linq to Objects, linq to SQl and linq to Entities.(I assume, but am not 100% sure that the latter is what Lightswitch uses). I plan to study a LOT more Linq, but just need to get this one query working.

So I have an entity called Items which lists every item in a job and it's serial no

So: Job.ID int, ID int, SerialNo long

I also have a Timesheets entity that contains shift dates, job no and start and end serial no produced

So Job.ID int, ShiftDate date, Shift int, StartNo long, EndNo long

When the user select a job from an autocomplete box, I want to look up the MAX(SerialNo) for that job in the timesheets entity. If that is null (i.e. none have been produced), I want to lookup the MIN(SerialNo) from the Items entity for that job (i.e. what's the first serial no they should produce)

I realize I need a first or default and need to specify the MIN(SerialNo) from Items as a default.

My Timesheet screen uses TimesheetProperty as it's datasource

I tried the following just to get the MAX(SerialNo) from Timesheets entity:

var maxSerialNo =
            (from ts in this.DataWorkspace.SQLData.Timesheets
             where ts.Job.ID == this.TimesheetProperty.Job.ID
             select ts.StartNo).Min();

but I get the following errors:

Instance argument: cannot convert from 'Microsoft.LightSwitch.IDataServiceQueryable' to 'System.Collections.Generic.IEnumerable

'Microsoft.LightSwitch.IDataServiceQueryable' does not contain a definition for 'Min' and the best extension method overload 'System.Linq.Enumerable.Min(System.Collections.Generic.IEnumerable)' has some invalid arguments

I also don't get why I can't use this:

var maxSerialNo = this.DataWorkspace.SQLData.Timesheets.Min(ts => ts.StartNo);

Can anyone point me in the right direction?

Thanks

Mark

Answer 1

IDataServiceQueryable doesn't support full set of LINQ operator like IEnumerable has.

IDataServiceQueryable – This is a LightSwitch-specific type that allows a restricted set of “LINQ-like” operators that are remote-able to the middle-tier and ultimately issued to the database server. This interface is the core of the LightSwitch query programming model. IDataServiceQueryable has a member to execute the query, which returns results that are IEnumerable. [Reference]

Possible solution is, execute your query first to get collection of type IEnumerable by calling .ToList(), then you can call .Min() against the first query result. But that isn't good idea if you have large amount of data, because .ToList() will retrieve all data match the query and do further processing in client side, which is inefficient.

Another way is, change your query using only operators supported by IDataServiceQueryable to avoid retrieving unnecessary data to client. For example, to get minimum StartNo you can try to use orderby descending then get the first data instead of using .Min() operator :

var minStartNo = 
            (
                 from ts in this.DataWorkspace.SQLData.Timesheets 
                 where ts.Job.ID == this.TimesheetProperty.Job.ID
                 orderby ts.StartNo descending select ts
            ).FirstOrDefault();