Join – ByATool

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.