CODEWITHSUNDEEP
sqllinqentity-frameworklinq-to-entities
Harindaka
Harindaka

Reputation: 4898

Left Outer Join in Linq to Entities / SQL

How can I write the following SQL in LINQ to Entities?

SELECT r.rolename,
       ( CASE
           WHEN ur.username IS NULL THEN 0
           ELSE 1
         END ) AS isinrole
FROM   bgt.roles r
       LEFT OUTER JOIN bgt.usersinroles ur
         ON ur.rolename = r.rolename
            AND ur.username = 'ADMIN'

Upvotes: 3

Views: 4248

Answers (3)

Istvan Heckl
Istvan Heckl

Reputation: 1111

Extension method for Left join (linq to entities):

            public static class DbSetExtensions
            {
                public static IQueryable<TResult2> LeftJoin<TOuter, TInner, TKey, TResult2>(
                    this IQueryable<TOuter> outerList,
                    IEnumerable<TInner> innerList,
                    Expression<Func<TOuter, TKey>> outerKeySelector,
                    Expression<Func<TInner, TKey>> innerKeySelector,
                    Expression<Func<LeftJoinTemp<TOuter, TInner>, TInner, TResult2>> resultSelector2)
                {
                    // (tr, historicPrices) => new { tr, historicPrices }
                    Expression<Func<TOuter, IEnumerable<TInner>, LeftJoinTemp<TOuter, TInner>>> myResultSelector1
                        = (tr, historicPrices) => new LeftJoinTemp<TOuter, TInner> { outer = tr, inners = historicPrices };

                    // e => e.historicPrices.DefaultIfEmpty()
                    Expression<Func<LeftJoinTemp<TOuter, TInner>, IEnumerable<TInner>>> myCollectionSelector
                        = e => e.inners.DefaultIfEmpty();

                    //var a = outerList.GroupJoin(innerList, outerKeySelector, innerKeySelector, resultSelector1);
                    var a = outerList.GroupJoin(innerList, outerKeySelector, innerKeySelector, myResultSelector1);

                    //return a.SelectMany(collectionSelector, resultSelector2);
                    var b = a.SelectMany(myCollectionSelector, resultSelector2);
                    return b;
                }
            }

            public class LeftJoinTemp<TOuter, TInner>
            {
                public TOuter outer;
                public IEnumerable<TInner> inners;
            }

example calling, left join of Transaction and HistoricPrice, for transaction you have to write parent.outer

            .LeftJoin(db.HistoricPrices,
                transaction => new { transaction.InstrumentId, DateId = transaction.Date2Id },
                historicPrice => new { historicPrice.InstrumentId, historicPrice.DateId },
                (parent, historicPrice) => new
                {
                    parent.outer.Id,    
                    parent.outer.OpeningDate,
                    parent.outer.InstrumentName,
                    historicPrice.DateId,
                    historicPrice.InstrumentId
                })

Upvotes: 0

Harindaka
Harindaka

Reputation: 4898

This worked for me. Thanks for all the suggestions.

var query = 
from r in Roles
from ur in UsersInRoles
.Where(v => v.Rolename == r.Rolename && v.Username == "ADMIN")
.DefaultIfEmpty()
select new { Rolename = r.Rolename, IsInRole = (ur.Username != null) };

The generated SQL is as follows

SELECT 
1 AS [C1], 
[Extent1].[Rolename] AS [Rolename], 
CASE WHEN ([Extent2].[Username] IS NOT NULL) THEN cast(1 as bit) WHEN ([Extent2].[Username] IS NULL) THEN cast(0 as bit) END AS [C2]
FROM  [bgt].[Roles] AS [Extent1]
LEFT OUTER JOIN [bgt].[UsersInRoles] AS [Extent2] ON ([Extent2].[Rolename] = [Extent1].[Rolename]) AND ('ADMIN' = [Extent2].[Username])

Upvotes: 3

svick
svick

Reputation: 245056

I would do it like this:

from role in db.Roles
let isInRole = role.UsersInRoles.Any(u => u.UserName == "ADMIN")
select new { role.RoleName, isInRole }

Althought the generated SQL is not as nice as yours.

Upvotes: 2

Related Questions