Linq Join Extension Method and How to Use It…
I don't like using the query syntax when it comes to Linq to AnythingButTheKitchenSink . Not sure why. Mostly, I guess, is that I seem to have a liking for Funcs and Actions to the point of stupidity and although you can work them into the query syntax, it just doesn't look right.
Now with most of the Linq methods like Where or First, it's simple once you understand lamdba expressions:
.SomeMethod(someField => someField.Property == value);
Now what about join?
So inner selector with an outer selector and a selector selects a selecting selector. Right got it.
Well let's try to break it down. First part is
this IEnumerable<TOuter>
So being that this is an extension method meaning this is the collection you are using this method on.
IEnumerable<TInner> inner
So second field must be the list you want to join to. Ok so far.
Func<TOuter, TKey> outerKeySelector
Now this is where it gets a little odd looking. We know we have Outer and Inner lists so there needs to be a way to join on something. Say Outer is User and Inner is UserAddress. Most likely you will have a UserID on both lists. If not, you do now. So basically what this part of the method is saying is "Give me the stupid key on the Outer (User) list that I should care about."
, user => user.UserID,
Next part:
Func<TInner, TKey> innerKeySelector
Pretty much the same thing, except now it needs the key from the Innerlist (UserAddress):
, address => address.UserID,
Now for the fun part:
Func<TOuter, TInner, TResult> resultSelector
Sa...say what? Ok this may look weird at first but you'll hate yourself for not seeing it. It's just asking you what to select from the two lists as some kind of hybrid object. See, you have to remember that with these linq methods, each method will produce a list. You can't just chain them together and have it remember every list you've made:
user.Where(user => user.UserID > 1) // gives me a list of users .Select(user => new { user.UserName, user.UserAddress, user.UserID } //Gives me new items with user name, address, and user id
From this simple method chain, the end list is NOT the same as the one you started with or the one produced by the where method.
The last part of the Join method needs you to tell it what it's going to produce from this join. Now it probably could just guess and include both lists, but that could be seen as sloppy and ultimately this gives you the choice of what exactly needs to be taken after the join. So:
, (user, address) => new { user, address});
So in this case, the newly created and joined list with be a list of items that have a user and address attached to it much like if you had a list of:
class UserAddressHybrid() { public User user { get; set; } public UserAddress userAddress { get; set; } }
So in other words, WHAT DO YOU WANT YOUR RESULTS TO LOOK LIKE?
In full it would look something like:
user.Join(address => address.User.UserID, //IEnumerable<TInner> inner user => user.UserID, //Func<TOuter, TKey> outerKeySelector address => address.UserID, //Func<TInner, TKey> innerKeySelector (user, address) => new { user, address}); //Func<TOuter, TInner, TResult> resultSelector
Not so hard anymore, is it? You can start kicking yourself now.
NHibernate Cross Join: The multi-part identifier … could not be bound..
Something else I found out about NHibernate, at least an older version, and cross joins... and if you're reading this chances are you have the same version. Say you want to cross join something but there are also other joins. You would think it should be like this:
SELECT
user.UserName, status.StatusName
FROM
User user, UserStatus status
LEFT JOIN
user.Address address
WHERE
user.CreatedDate = status.CreatedDate
Ok so once again, this query is kind of stupid but the idea is there. With NHibernate you have to Cross Join when you need to match two objects but not on the mapped keys. Meaning that User and Status might be mapped on UserID ie Status.UserID = User.UserID. But what if you wanted to match them on something else, like a date. Well that's where the cross join came in. The Sql might look like:
SELECT user0_.UserName, status0_.StatusName FROM ByATool.User user0_, ByATool.UserStatus status0_ LEFT JOIN ByATool.Address address0_ ON address0_.UserID = user0_.UserID WHERE user0_.CreatedDate = status0_CreatedDate
Looks fine right? Well it's not according to SqlServer 2005. Turns out the cross join has to appear AFTER the other joins. So in reality it should look like:
SELECT user0_.UserName FROM ByATool.User user0_, ByATool.UserStatus status0_ LEFT JOIN ByATool.Address address0_ ON address0_.UserID = user0_.UserID, status0_.StatusName WHERE user0_.CreatedDate = status0_CreatedDate
Which means the hql looks like this:
SELECT
user.UserName
FROM
User user, UserStatus status
LEFT JOIN
user.Address address,
status.StatusName
WHERE
user.CreatedDate = status.CreatedDate
See how the cross joined object is now after the Left Join? Looks odd but this is what it takes to get the cross join through. Kind of annoying.
NHibernate.QueryException: expecting ‘elements’ or ‘indices’ after…
Yes, it's true. I have two lovers. Well more like I'm stuck with NHibernate due to some kind of stupid forced marriage and the other is my somewhat quirky but ultimately young and hot mistress Entity Framework. And now that I've admitted to such an atrocity you are about to run screaming, I'll get on with what the hell this means.
So I ran across this the other day and well I'm sure a smart person would immediately understand what it means, but that smart person doesn't write for this blog. You're stuck with me. So after some staring at the screen and praying to whatever god I just learned about on wikipedia, I figured it out. Now I can't promise this is the only reason to get this error, but this is the answer I have.
Say you have a user and with that user you have a list of events. Now it should be obvious that there is an event class, an event table, and user table. Simple enough. Now in hql, it would look something like this:
SELECT
user
FROM
User user
LEFT JOIN
user.Events event
WHERE
user.Events.Status = SomeStatusEnum.SomeValue
And yes I realize that it doesn't need to be a left join, but just roll with me on this because I don't feel like making a better example. So if you look close enough at the query you should see something wrong... Why would I join the objects and then refer to the event status through the user?
user.Events.Status = SomeStatusEnum.SomeValue
Because I'm a tool. Yeah turns out that, to me, crytic message is really saying "Use the joined object alias you f-ing tool." In other words:
events.Status = SomeStatusEnum.SomeValue
And boom. No more error.
Join By Anonymous Types in Linq
Just found this out yesterday so I thought I would post and pass on to all two of you reading this.
Suppose you have a User table and a Contacts table and you wanted to find all the users that match up with the contacts table. Now suppose there is no direct correlation. What to do? You could do something really brilliant by joining the tables together on FirstName and LastName, because we all know that there will always only be one John Smith in either table. Screw you, I couldn't think of a better example at the time.
public static List<User> GetAllUsersWithMatchingContactInformationUsingJoin() { List<User> foundUsers; var query = from user in dataContext.Users join contact in dataContext.Contacts on new {user.FirstName, user.LastName } equals new { contact.FirstName, contact.LastName } select user; foundUsers = query.ToList(); return foundUsers; }
As you can see here:
join contact in dataContext.Contacts on new {user.FirstName, user.LastName } equals new { contact.FirstName, contact.LastName }
You can create a type on the fly and then compare it to another. I thought that was interesting.
Links
Random Quote
Curtiss: U R TEH STUPID 1!
Curtiss: It is true cuz it’s in all caps.
Recent
- Child IFrame Page Interacting with Parent Page… Yeah I went there.
- Apple’s Latest Innovation Is Astounding, A Real Game Changer
- Get the jQuery DatePicker to Work With jQuery Modal Dialog
- ByATool.com Is Looking For Writers
- Dear Microsoft,
Categories
Tags
.Net Action Ajax Ajax Toolkit Anonymous Methods Anonymous Types ASP.Net Attributes Autocomplete C# Charting Closures Covariance CSS Dynamic Dynamic Controls Entity Framework Enumeration Expression Extension Methods FizzBuzz Func Generics Hash Hql Javascript Join jQuery Lambda Like Linq MVC Order By Paging Random Reflection ScriptControl Select String Stupid Test Thoughts Validation WebControl WebMethod
WP Cumulus Flash tag cloud by Roy Tanck and Luke Morton requires Flash Player 9 or better.
Reviews
