Linq JOIN

Apr 11, 2011 at 8:19 PM

Hello, I'm new to Linq to EF.

I have a new project using Entity Framework 4 and I quickly found out that unless you're using Compiled Queries, performance is pretty bad, which led me to your project.

It seems like a dream come true, however, I have hit a problem:

            var query = from sm in db.Table1.AsCompilable()
                        join sy in db.Table2.AsCompilable() on sm.T1Id equals sy.T1Id
                        join sss in db.Table3.AsCompilable() on sm.T3Id equals sss.T3Id
                        where sm.pId == myId &&
                              sss.FinalStage == false &&
                              (now >= sy.BeginDate && now <= sy.EndDate)
                        select sm;

return query.SingleOrDefault()
This will throw an exception: "The parameter '__context' was not bound in the specified LINQ to Entities query expression."
I even tried only calling AsCompilable() only on Table1 but that yields the same exception.
 
I found a workaround which was to do:
 
var query = from sm in db.Table1.AsCompilable()
                        from sy in db.Table2
                        from sss in db.Table3
                        where /* (perform the join explicitly) */

That query seems to work and I can confirm that my performance is now through the roof!! So thank you very much for creating this solution. 
It's much better than creating a static class for every single query.
PS: I realize that I am not using the navigation properties for the relationship from Table1 -> Table2 -> Table3, 
but it is a requirement of the project that the Entity model is not under my control. Even if I could use the table relationships,
I still thought I'd report this, in case it is actually a bug.
Coordinator
Apr 25, 2011 at 7:09 PM

Hi!

Sorry for taking so long to respond. I thought I would be notified of new posts in the forum, but I guess not..  Glad to hear that you find the project useful! Please let me now if you stumble upon other issues.

The query you mentioned was indeed something I hadn't tested, so it was a bug, but it was easy to solve and the latest version should work just fine. You can write the query without the AsCompilable (if you include them they will be ignored)

var query = from sm in db.Table1.AsCompilable()
                        join sy in db.Table2 on sm.T1Id equals sy.T1Id
                        join sss in db.Table3 on sm.T3Id equals sss.T3Id
                        where sm.pId == myId &&
                              sss.FinalStage == false &&
                              (now >= sy.BeginDate && now <= sy.EndDate)
                        select sm;

 

Best regards

Gunnar

Apr 25, 2011 at 7:21 PM

Oh, wow. Thank you so much for the fix.

I'm not at work right now but as soon as I can, I will try it. All I can say is you have no idea how much time this project has saved me. I wish more people were aware of it!