CODEWITHSUNDEEP
c#linqvisual-studio-lightswitch
Uooo
Uooo

Reputation: 6334

AsQueryable() does not return needed type in custom LINQ query using Lightswitch

I am using Lightswitch to build my application and I have the following problem.

In my database, I have three tables:

Article and Provider have a many-to-many relation, therefore junction table ArticleProvider is needed.

Now, I want a screen in my application where the user can choose a provider and sees all articles which have a relation to this provider.

Using SQL, I would to it like this (123 is the Provider_Id I want to select).

SELECT * 
FROM Article a 
WHERE a.Id IN 
    (SELECT ap.Article_Id FROM ArticleProvider ap WHERE ap.Provider_Id=123)

In my Lightswitch application, I created a Query by clicking on the "Articles" Table in my Datasource and choosed "Add Query". I added a parameter ProviderId and switched to the source code editor to create my custom query:

partial void ArticleByProvider_PreprocessQuery(int? ProviderId, 
                                               ref IQueryable<Article> query)
{ 
    ...
}

Next I started to create my Linq Query. I think I need an IQueryable<ArticleProvider> Query to filter by them, so I tried:

(from art in query select art.ProviderQuery).AsQueryable<ArticleProvider>()

But, when trying this, I get a compile time error saying that this type can not be converted. So I tried this and it compiles fine:

(from art in query select art.ProviderQuery)
    .AsQueryable<IDataServiceQueryable<ArticleProvider>>()

However, when using the returned IQueryable apList in my next query:

from ap in apList where ap.Provider.Id == 123 select ap.Article.Id

It seems that the fields Provider and Article can not be found. Also Visual Studio's code completion does not suggest these fields, only lots of methods and fields which are not in my database.

How can I solve this problem?

I played around with casts and other method calls like ToList(), but I get always stuck at this point. I am new to Linq and C#. Thank you in advance for any help.

EDIT: I checked the return type of the first query by using: var temp = (from art in query select art.ProviderQuery).AsQueryable()

The returned type is System.Linq.IQueryable<Microsoft.LightSwitch.IDataServiceQueryable<LightSwitchApplication.ArticleProvider>>

Upvotes: 1

Views: 1443

Answers (2)

Bryan Hong
Bryan Hong

Reputation: 1483

Give this query a try and see if this works:

partial void ArticleByProvider_PreprocessQuery(int? ProviderId, 
                                               ref IQueryable<Article> query)
{
     query.Where(art => art.ArticleProviders
                              .Any(artProv => artProv.Provider.Id == ProviderId));
}

The idea is to get all Articles that at least matches the Provider Id.

Note: Haven't tested this code myself. But the idea should be there.

Upvotes: 0

user2021497
user2021497

Reputation: 11

Your problem is that you are in the PreprocessQuery This is for filtering data further, not adding extra.

If you look around a little this is mentioned a lot.

Upvotes: 1

Related Questions