Convert advanced SQL query with nested joins to Linq-to-sql

Question

I have ran into a snag with my Linq-to-Sql.

I have a sql query that runs the way I want and usually I use Linqer to convert to Linq to see the general idea. But this time my SQL query seems to advanced for Linqer. :/

I think the problem is the INNER JOINS that are nested in the LEFT OUTER JOIN. Unfortunately I have never ran into this before and don't know how to solve it using Linq.

My SQL query looks like this:

SELECT c.[Company], c.[Name_First], c.[Name_Last], ort.[IDOriginatorRoleType], 
       ort.[RoleType] AS [OriginatorRoleType], o.[IDOriginator], o.[IDWork], 
       o.[IDContact], m.[IDMedia], m.[IDWork], m.[FileName], m.[FileNameOnDisk], 
       m.[DateAdded], w.[IDWork] AS [IDWork2], w.[ArticleNumber], w.[Title], 
       w.[FrontPageLow], w.[FrontPageLowOnDisk], w.[FrontPageHigh], 
       w.[FrontPageHighOnDisk]
FROM [dbo].[tblSubscriptionsWorks] AS sw
INNER JOIN [dbo].[tblWorks] AS w ON sw.[IDWork] = w.[IDWork]
LEFT OUTER JOIN [dbo].[tblMedias] AS m ON m.[IDWork] = w.[IDWork]
LEFT OUTER JOIN ([dbo].[tblOriginators] AS o
    INNER JOIN [dbo].[tblOriginatorRoles] AS ors ON 
          o.[IDOriginatorRole] = ors.[IDOriginatorRole]
    INNER JOIN [dbo].[tblOriginatorRoleTypes] AS ort ON 
          ors.[IDOriginatorRoleType] = ort.[IDOriginatorRoleType]
    INNER JOIN [dbo].[tblContacts] AS c ON 
          o.[IDContact] = c.[IDContact]) ON 
(o.[IDWork] = w.[IDWork]) AND (ort.[IDOriginatorRoleType] = 1)
WHERE sw.[IDWork_Subscription] = 9942

I would consider those joins to be 'chained' rather than 'nested'. — onedaywhen

Arion Arion · Accepted Answer · 2012-01-18T09:15:33

The left outer join is not a problem what I can see. You just have to divide the statement

LEFT OUTER JOIN ([dbo].[tblOriginators] AS o
    INNER JOIN [dbo].[tblOriginatorRoles] AS ors ON 
          o.[IDOriginatorRole] = ors.[IDOriginatorRole]
    INNER JOIN [dbo].[tblOriginatorRoleTypes] AS ort ON 
          ors.[IDOriginatorRoleType] = ort.[IDOriginatorRoleType]
    INNER JOIN [dbo].[tblContacts] AS c ON 
          o.[IDContact] = c.[IDContact]) ON 
(o.[IDWork] = w.[IDWork]) AND (ort.[IDOriginatorRoleType] = 1)

into another IQueryable list. In the example the variable db is the datacontext. Here is a suggestion to a solution:

//selects all the columns that is just in the select from the left join
var leftJoin=
                (
                    from o in db.tblOriginators
                    join ors in db.tblOriginatorRoles
                        on o.IDOriginatorRole equals ors.IDOriginatorRole
                    join ort in db.tblOriginatorRoleTypes
                        on ors.IDOriginatorRoleType equals ort.IDOriginatorRoleType
                    join c in db.tblContacts
                        on o.IDContact equals c.IDContact
                    where ort.IDOriginatorRoleType==1
                    select new
                    {
                        o.IDWork,
                        c.Company,
                        c.Name_First,
                        c.Name_Last,
                        ort.IDOriginatorRoleType,
                        ort.RoleType,
                        o.IDOriginator,
                        o.IDContact
                    }
                );
var output=(
        from sw in db.tblSubscriptionsWorks
        join w in db.tblWorks
            on sw.IDWork equals w.IDWork
        from m in db.tblMedias
            .Where(x=>x.IDWork==w.IDWork).DefaultIfEmpty()
        //Left join with the IQueryable list
        from org in leftJoin
            .Where(x =>x.IDWork==w.IDWork).DefaultIfEmpty()
        where
            sw.IDWork_Subscription == 9942
        select new
        {
            org.Company,
            org.Name_First,
            org.Name_Last,
            org.IDOriginatorRoleType,
            OriginatorRoleType=org.RoleType,
            org.IDOriginator,
            org.IDWork,
            m.IDMedia,
            m.IDWork,
            m.FileName,
            m.FileNameOnDisk,
            w.FrontPageLow,
            w.FrontPageLowOnDisk,
            w.FrontPageHigh,
            w.FrontPageHighOnDisk
        }
    );

Convert advanced SQL query with nested joins to Linq-to-sql

1 Answers