CODEWITHSUNDEEP
entity-frameworksilverlight-4.0wcf-ria-servicesria
Mcad001
Mcad001

Reputation: 299

Join two tables and filter result using EF 4 and RIA services

Public Function LoadSiteInfo(ByVal sId As Integer) As IQueryable(Of Site)
    Return Me.ObjectContext.Sites.Include("SiteData").Where((Function(f) f.SiteID = sId) AndAlso Function(x) x.SiteData.SiteUpdateDate < today)) 
End Function

So, I'm trying to filter on SiteId from the Sites table, ANDALSO on the SiteUpdateDate in the SiteData table, it's the last part where I cannot get the syntax correct - it's just saying that SiteUpdateDate attribute is not a member of ObjectContext.Site, which is correct its part of SiteData

How can I filter on attributes in the included table SiteData? Or how can I rewrite this and still return an Iquerable of Site with SiteData childtable beeing filtered as I want? Should be really easy, but I'm struggling, starting to believe that include a filtered child collection of a selected parent is not allowed... :(

Upvotes: 0

Views: 740

Answers (2)

RPM1984
RPM1984

Reputation: 73123

I'm not good with VB, so here's the C# version.

return this.ObjectContext
   .Sites
   .Where(x => x.SiteData.All(y => y.SiteUpdateData < today))
   .Where(x => x.SiteId == sId)
   .Select(x => new { Sites = x, SiteDatas = x.SiteData })
   .Select(x => x.Sites);

The trick is, you can't filter on an eager loaded property (SiteData), so you have to use anonymous type projection, and then project the query back into what you want.

Upvotes: 1

ecathell
ecathell

Reputation: 1030

I dont believe you can do it this way. You have to first return your parent with child collections.

Public Function LoadSiteInfo(ByVal sId As Integer) As IQueryable(Of Site)
     return FillSiteInfo(sId).Where(Function(x) x.SiteData.SiteUpdateDate < today))
End Function 

Public Function FillSiteInfo(byVal sId as Integer) as IQueryable(of Site)
    Return Me.ObjectContext.Sites.Include("SiteData").Where((Function(f) f.SiteID = sId).AsQueryable()
End Function

That Should work for you.

also you need to check your RIA services to make sure your Child Collection has [include] as an attribute.

Upvotes: 1

Related Questions