StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POReusing a join in LINQ
text
Body
copied!<p>I have a number of queries (in a C# project) that all use the same join syntax, is there a simple way to move the join out of the query an into a 'function' or 'template' (for want of a better word).</p> <p>The current query looks like this (rc is my ObjectContext)</p> <pre><code>var x = from b in rc.Pressures join g in rc.GUIDIdentityMaps on b.UserIdentity equals g.UserIdentity where g.UserId == userId orderby b.Recorded descending select b; </code></pre> <p>I'd like to be able to take the join out and put it in a 'function' called something like 'PressuresJoin'</p> <pre><code>from b in rc.Pressures join g in rc.GUIDIdentityMaps on b.UserIdentity equals g.UserIdentity where g.UserId == userId </code></pre> <p>and then use something like</p> <pre><code>var x = PressuresJoin() orderby b.Recorded descending select b; </code></pre> <p><strong>Update :</strong> Following @Xanatos' directions I created an IQueryable function like this.</p> <pre><code>public static IQueryable<Pressure> PressuresJoin(ObjectContext rc, Guid userId) { return from b in rc.Pressures join g in rc.GUIDIdentityMaps on b.UserIdentity equals g.UserIdentity where g.UserId == userId select b; } </code></pre> <p>which I then used like this</p> <pre><code> IQueryable<Pressure> q = PressuresJoin(rc, userId); var y = from b in q orderby b.Recorded descending select b; </code></pre> <p>so using the IQueryable now means that I don't need to write the same join code over and over and nor do I need to ensure that the Pressures coming from the DB are restricted to the correct user (as the PressuresJoin ensures this always happens).</p> <p>Notice that I didn't need to create an extra class (as suggested) because I don't need any of the GUIDIdentityMaps information AFTER the join is made.</p> <p>Very neat and tidy - thanks to all contributors</p> <p><strong>Update II :</strong> Following the answer provided by Kirk I moved away from the 'manual join' syntax and looked at using the navigation property.</p> <p>This led me to a couple of choices The first one was to use the .Include like this</p> <pre><code>public static IQueryable<Pressure> PressuresJoinInclude(ObjectContext rc, Guid userId) { return from b in rc.Pressures.Include("GuidIdentityMap") where b.GUIDIdentityMap.UserId == userId select b; } </code></pre> <p>and call it like this</p> <pre><code>IQueryable<Pressure> q = PressuresJoinInclude(rc, userId); var y = from b in q orderby b.Recorded descending select b; </code></pre> <p>That works just dandy (so Kirks answer is also the answer) but I don't really like the .Include(string) syntax and it caught me out as I'd mis-spelt the GUIDIdentityMap.</p> <p>Then I looked at ways to avoid using .Include(string) and came up with this</p> <pre><code>public static IQueryable<Pressure> PressuresJoinDirect(ObjectContext rc, Guid userId) { return from b in rc.GUIDIdentityMaps.Single(i => i.UserId == userId).Pressures.AsQueryable() select b; } </code></pre> <p>notice the 'AsQueryable() at the end (casting it from IEnumerable back to IQueryable) This is called like</p> <pre><code>IQueryable<Pressure> q = PressuresJoinDirect(rc, userId); var y = from b in q orderby b.Recorded descending select b; </code></pre> <p>So far I think this is my favourite, although it would be nicer if Pressures was the 'first' object in the statement instead of GUIDIdentityMaps, but I've not checked to see what SQL is produced or what performace differences there are (if any)</p> <p>Now I'm going to look at Jims .(dot) solution. </p> <p><strong>Update III :</strong> Hopefully I've understood the dot notation correctly, and here is my 'join' by using the Navigation already built in to the objects. My IQueryable function looks like this</p> <pre><code> public static IQueryable<Pressure> PressuresJoinDot(ObjectContext rc, Guid userId) { return from b in rc.Pressures where b.GUIDIdentityMap.UserId == userId select b; } </code></pre> <p>and using it is as simple as the others</p> <pre><code> IQueryable<Pressure> q = PressuresJoinDot(rc, userId); var y = from b in q orderby b.Recorded descending select b; </code></pre> <p>At the end of all of this I do a Skip/Take like this</p> <pre><code> IQueryable<Pressure> z = y.Skip(startRow).Take(rows); List<Pressure> l = z.ToList(); </code></pre> <p>All four of these options above work and the end result of any of these queries is the same set of records. To my surprise they don't all produce the same SQL and they don't all go at the same speed (hence why I split the Skip/Take and ToList()).</p> <p>The plain old Join and the Dot version perform at similar speeds, with the plain Join being a bit quicker probably. The Include version performs well but has an increasing number of DB reads as you Skip/Take up into the list of records. The Direct method is woefully slow, more than 10 fold on the other methods and I'm sure that the Skip/Take is performed on the List() generated rather than on the database.</p> <p>Certainly opened my eyes to how easy it would be to choose the wrong LINQ, and certainly choosing it on the basis that it looks 'the nicest', the Direct method, would not be the best idea.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload