CODEWITHSUNDEEP
c#linqvisual-studio-lightswitch
Yasen Simeonov
Yasen Simeonov

Reputation: 1

Lightswitch - C# LINQ Preprocess Query

I'm pretty new to LINQ and I'm having trouble restricting the number of values (strings in "yyyy-mm" format) from my "Availabilities" entity which are shown on a Lightswitch screen, based on a Query ("LINQ_Query"). The idea is to show only "Availabilities" which are recent - for example where int.Parse(av.Availability.Substring(0,4)) > 2013

The reason I'm doing this is that "Availability" entity contains strings and not all of them are in "yyyy-MM" format - there are also entries like "Available", "Delayed", "2014" etc. I want to filter only the ones which have a length of 7 and ideally are more recent than 2013-12 and not in the future (Availability > current date).

In SQL I have achieved this with the following code:

SELECT Availability FROM Availabilities where LEN(Availability)=7 and LEFT(Availability,4) > 2013 and availability<=CONVERT(varchar(7),GETDATE(),126) order by Availability desc

The C# code I use for my PreprocessQuery is the following and it compiles with no errors:

partial void LINQ_Query_PreprocessQuery(ref IQueryable<Availabilities> query)
    {
        query = from av in query
                where av.Availability.Length==7 && int.Parse(av.Availability.Substring(0,4)) > 2013
                select av;
    }

However, when I deploy and test there are the dreaded red Xs on the screen in question. I enabled tracing and I see the error is:

[Microsoft.LightSwitch.DataService][Application:Error][LightSwitchApplication.ApplicationData:LINQ_Query] An exception has occurred:  Microsoft.LightSwitch.DataServiceOperationException: LINQ to Entities does not recognize the method 'Int32 Parse(System.String)' method, and this method cannot be translated into a store expression.

Is there a workaround for this? I was thinking of executing a separate "IDataServiceQueryable" query and then pointing the Preprocess IQueryable query to the results of the "IDataServiceQueryable" one. Is there any chance this or another type of query would work with int.Parse or DataTime.Parse/ParseExact?

Thanks in advance!

P.S. Some more details about my setup: I'm testing a Web Lightswitch 2011 application, hosted on my PC as IIS Server. The Database is hosted on a SQL Server 2008 Express x64 SP3 also on my PC, which leads me to anothe question - since LINQ to Entities doesn't support the functions I need, can I create a LINQ to SQL query on the Availabilities table?

Upvotes: 0

Views: 922

Answers (1)

Johan Van Wambeke
Johan Van Wambeke

Reputation: 66

Try splitting them up and removing the cast

partial void LINQ_Query_PreprocessQuery(ref IQueryable<Availabilities> query)
    {
        query = query.Where(x=>x.Availability.Length==7);
        query = query.Where(x=>x.Availability.Substring(0,4) == "2013";
    }

Ctype or Parse in Linq could be trouble (if it would fail for one, it fails for all)

If it works, join the 2 queries again.

Upvotes: 1

Related Questions