Attention: We are retiring the ASP.NET Community Blogs. Learn more >

Contents tagged with Linq

  • Linq2Sql: How to join tables on more than one column

    No Comments

    You can join two tables in Linq2Sql by using an anonymous type to specify the join. 

    var r =
        from o in db.Orders
        join p in db.Products on o.ProductId equals p.ProductId
        join pu in db.ProductUsers on new { p.ProductId, o.UserId } equals new { pu.ProductId, pu.UserId }
        select new {o, p, pu};

    This is equivalent to the following SQL:

    SELECT * FROM Order o
    JOIN Product p ON o.ProductId=p.ProductId
    JOIN ProductUser pu ON p.ProductId=pu.ProductId AND o.UserId=pu.UserId

    The anonymous type { p.ProductId, o.UserId } is lined up with the second anonymous type { pu.ProductId, pu.UserId } to create the join on both columns.

    But what if you are trying to join two tables where the column names do not match?  For instance this SQL:

    SELECT * FROM Order o
    JOIN Product p ON o.ProductId=p.ProductId
    JOIN ProductUser pu ON p.ProductId=pu.ProductId AND o.UserId=pu.CreatedByUserId

    You would think you could create two anonymous types just like above and line up the properties like this:
    First anonymous type: { p.ProductId, o.UserId } 
    Second anonymous type: { pu.ProductId, pu.CreatedByUserId }

    But if you try that you will get a compilation error:

    The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'Join'.

    What is going on here?  Both o.UserId and pu.CreatedByUserId have the same data type so why is there a compilation error? 

    Because Linq2Sql lines up the two anonymous types by name and with the different names it cannot resolve the two column join.  Both the order and the name need to match for this to work.  To resolve this just specify an alias property name for one of the anonymous types that do not match like so:
    First anonymous type: { p.ProductId, CreatedByUserId = o.UserId }
    Second anonymous type: { pu.ProductId, pu.CreatedByUserId }

    Now Linq2Sql knows how to completely match up the two column join.  Here is the full Linq2Sql statement for a join with two columns when the column names do not match:

    var r =
        from o in db.Orders
        join p in db.Products on o.ProductId equals p.ProductId
        join pu in db.ProductUsers on new { p.ProductId, CreatedByUserId = o.UserId } equals new { pu.ProductId, pu.CreatedByUserId}
        select new {o, p, pu};

    Technorati Tags:


  • How to get an indexed item of an IEnumerable object (Linq)

    1 Comment

    If you have an IEnumerable<T> collection of some objects T and you need to get a particular item out of that collection, you cannot use the standard indexing that you would normally use with brackets ([index]).  If you try you will get an error such as:

    Cannot apply indexing with [] to an expression of type ‘System.Collections.Generic.IEnumerable<T>

    image

    But there is the extension method ElementAt(index) (in the System.Linq namespace) for IEnumerable<T> that will allow you to get at that particular indexed item:

    MyItem = MyIEnumerableExpression.ElementAt(index);

    From the MSDN Documentation: http://msdn.microsoft.com/en-us/library/bb299233.aspx

    image