LINQ "Method Join Not Supported" gone when put "AsEnumerable"

Question

I've a WCF which query data from data entities. I'm kind of confuse why it's complain "Method Join Not Supported" if the syntax did not put "AsEnumerable", and it there a way not using AsEnumerable. Because i read some article mentioned it will put all data before execute the "where" condition.

        Dim ent As DataEnties.DPPromoterEntities
        Dim que = Nothing
        Dim sRet As String = ""

        Try
            ent = New DataEnties.DPPromoterEntities(New Uri(AppSettings.Item("Data")))

            que = From CHS In ent.RC_CampaignHubSpokeTbl.AsEnumerable  '<--This line 
                  Join Cam In ent.RC_CampaignTbl.AsEnumerable On Cam.intCampaign Equals CHS.intCampaign'<--This line 
                  Where Cam.tintStatus.Equals(1)
                  Select New With {CHS.intCampaign,
                                   CHS.intCouponRefHub,
                                   CHS.intCouponRefSpoke,
                                   CHS.intHubRef,
                                   CHS.intSpokeRef}
            sRet = New JavaScriptSerializer().Serialize(que)

        Catch ex As Exception
            clsLog4Net.WriteLog(System.Reflection.MethodBase.GetCurrentMethod.Name.ToString, ex.Message, True)
        End Try

MarcinJuraszek · Accepted Answer · 2013-04-04T09:54:52.410

0

AsEnumerable force the Join statement execution into LINQ to Objects. So all data from RC_CampaignHubSpokeTbl and RC_CampaignTbl will be taken from WCF, and then combined together within your application.

It's necessary because WCF calls do not support joins.

To optimize your query a bit you can do following:

que = From CHS In ent.RC_CampaignHubSpokeTbl.AsEnumerable()
      Join Cam In ent.RC_CampaignTbl.Where(Function(c) c.tintStatus.Equals(1)).AsEnumerable() On Cam.intCampaign Equals CHS.intCampaign
      Select New With {CHS.intCampaign,
                       CHS.intCouponRefHub,
                       CHS.intCouponRefSpoke,
                       CHS.intHubRef,
                       CHS.intSpokeRef}

Further possible optimization - not sure Contains() is available in WCF calls, so you have to check it by yourself.

Dim compains = ent.RC_CampaignTbl.Where(Function(c) c.tintStatus.Equals(1)).ToArray()
Dim HubSpokes = ent.RC_CampaignHubSpokeTbl.Where(Function(h) compains.Contains(h.intCampaign)).ToArray()

que = From CHS In compains 
      Join Cam In HubSpokes  On Cam.intCampaign Equals CHS.intCampaign
      Select New With {CHS.intCampaign,
                       CHS.intCouponRefHub,
                       CHS.intCouponRefSpoke,
                       CHS.intHubRef,
                       CHS.intSpokeRef}

edited Apr 04 '13 at 09:54

answered Apr 04 '13 at 09:38

MarcinJuraszek

124,003
15
196
263

will this reduce the number of lines return? For the above code, RC_CampaignHubSpokeTbl still return all lines and RC_CampaignTbl will return only tintstatus = 1? – tsohtan Apr 04 '13 at 09:47
Yes, but only from one table. It will load all HubSpokes, but only necessary Campains. – MarcinJuraszek Apr 04 '13 at 09:49
OK. Thx. hmmm... I think it will have performance issue in long run if the data grow. – tsohtan Apr 04 '13 at 09:52
I've posted another version, where only necessary data should be downloaded, but I'm not sure it's gonna work. You have to try it by yourself. – MarcinJuraszek Apr 04 '13 at 09:55
Thanks. But not working. it's say "Where" cant be call with these argument. – tsohtan Apr 04 '13 at 10:08

LINQ "Method Join Not Supported" gone when put "AsEnumerable"

1 Answers1