Converting SQL Statement with Multiple Left Outer Joins and Various Clauses to LINQ

I've been trying to create associations and navigation properties to simplify my LINQ query a little; however, because of the complicated, proprietary database I'm working with it's been really difficult. For most of this process, I've been using the database first approach on EF5. I couldn't create associations and navigation properties using database first, so I tried to create foreign keys on SQL Server, but was unsuccessful in that. For my next step, I tried to create the tables, associations, and navigation properties using the code first approach; however, again, because of the necessities of the database and server I'm working with, I was unsuccessful in simplifying my query. My last resort is just to do what I was originally trying to do in this question. That is to get this LINQ query: from item in db2.OrderFormDump join icp in db2.IcPricP on item.NODASHITEMNO equals icp.ITEMNO into icpGroup from iG in icpGroup.DefaultIfEmpty() join itemInfo in db2.WebItemInfo on item.ITEMNO equals itemInfo.ITEMNO into itemInfoGroup from iIG in itemInfoGroup.DefaultIfEmpty() join weboeordh in db2.WebOEOrdH on "brian" equals weboeordh.USER into weboeordhGroup from wOEODHG in weboeordhGroup.DefaultIfEmpty() join weboeordd in db2.WebOEOrdD on new { itemno = item.NODASHITEMNO, orduniq = wOEODHG.ORDUNIQ } equals new { itemno = weboeordd.ITEMNO, orduniq = weboeordd.ORDUNIQ } into weboeorddGroup from wOEODG in weboeorddGroup.DefaultIfEmpty() join weboeordsubmit in db2.WebOEOrdSubmit on wOEODG.ORDUNIQ equals weboeordsubmit.ORDUNIQ into weboeordsubmitGroup from wOEOSG in weboeordsubmitGroup.DefaultIfEmpty() join webloginaccess in db2.WebLoginAccess on "brian" equals webloginaccess.USER into webloginaccessGroup from wLAG in webloginaccessGroup.DefaultIfEmpty() join arcus in db2.Arcus on wLAG.CUSTID equals arcus.IDCUST into arcusGroup from aG in arcusGroup where (item.ALLOWINBC == "Yes" && item.ALLOWINAB == "Yes") && (item.BASEDESCRIPTION.Contains("dude") || item.DESCRIPTION.Contains("dude") || item.CATEGORY.Contains("dude") || item.FOODACCSPEC.Contains("dude") || item.ITEMBRAND.Contains("dude") || item.ITEMGROUP.Contains("dude") || item.ITEMNO.Contains("dude") || item.ITEMSUBTYPE.Contains("dude") || item.ITEMTYPE.Contains("dude") || iIG.INFO.Contains("dude") || item.UPC.Contains("dude") || item.UPC.Substring(2, 10).Contains("dude")) && (iG.CURRENCY == "CDN" && iG.DPRICETYPE == 1) && wOEODG.ORDUNIQ != wOEODHG.ORDUNIQ && iG.PRICELIST == aG.PRICLIST orderby item.BASEDESCRIPTION select new { item.ITEMNO, item.BASEDESCRIPTION, iIG.INFO, item.UPC, iG.UNITPRICE, item.CASEQTY, wOEODG.QTY } into x group x by new { x.ITEMNO, x.BASEDESCRIPTION, x.INFO, x.UPC, x.UNITPRICE, x.CASEQTY, x.QTY } into items select items; To get the same results as this SQL query: DECLARE @search varchar(50) = 'dude' SELECT orderformdump.itemno,basedescription,info,upc,CAST(UNITPRICE AS DECIMAL(18,2)),caseqty, sum(qty) AS userquantity FROM PPPLTD.[dbo].[ORDERFORMDUMP] LEFT JOIN PPPLTD.dbo.ICPRICP ON replace(PPPLTD.[dbo].[ORDERFORMDUMP].[ITEMNO],'-','') = ICPRICP.ITEMNO LEFT JOIN PPPLTD.dbo.WEBITEMINFO ON ORDERFORMDUMP.ITEMNO = WEBITEMINFO.ITEMNO LEFT JOIN pppltd.dbo.weboeordh ON [user] = 'brian' LEFT JOIN pppltd.dbo.weboeordd ON pppltd.dbo.WEBOEORDD.ITEMNO = REPLACE(pppltd.dbo.ORDERFORMDUMP.ITEMNO,'-','') and weboeordd.ORDUNIQ = weboeordh.orduniq Left JOIN pppltd.dbo.weboeordsubmit ON weboeordsubmit.orduniq = weboeordd.ORDUNIQ and weboeordd.ORDUNIQ != weboeordsubmit.orduniq LEFT JOIN PPPLTD.dbo.WEBLOGINACCESS ON WEBLOGINACCESS.[USER] = 'brian' LEFT JOIN PPPLTD.dbo.ARCUS ON ARCUS.IDCUST = WEBLOGINACCESS.CUSTID where (allowinbc = 'Yes' or allowinab = 'Yes') AND [PRICELIST] = ARCUS.PRICLIST and [CURRENCY] = 'CDN' and DPRICETYPE = 1 and (itemgroup like '%' + @search + '%' or itemtype like '%' + @search + '%' or itembrand like '%' + @search + '%' or subcat like '%' + @search + '%' or orderformdump.description like '%' + @search + '%' or basedescription like '%'+ @search + '%' or orderformdump.ITEMNO like '%'+@search+'%' or UPC like '%'+@search+'%' or (select top 1 1 from pppltd.dbo.ICITEMO where OPTFIELD like 'UPC%' and VALUE like '%'+@search+'%' and ITEMNO = pppltd.dbo.ORDERFORMDUMP.itemno) is not null) group by ORDERFORMDUMP.ITEMNO,BASEDESCRIPTION,info,UPC,CAST(UNITPRICE AS DECIMAL(18,2)),caseqty order by basedescription When I execute the LINQ on LINQPad, it produces this SQL: DECLARE @p0 NVarChar(1000) = '-' DECLARE @p1 NVarChar(1000) = '' DECLARE @p2 VarChar(1000) = 'brian' DECLARE @p3 NVarChar(1000) = '-' DECLARE @p4 NVarChar(1000) = '' DECLARE @p5 VarChar(1000) = 'brian' DECLARE @p6 VarChar(1000) = 'Yes' DECLARE @p7 VarChar(1000) = 'Yes' DECLARE @p8 VarChar(1000) = '%dude%' DECLARE @p9 VarChar(1000) = '%dude%' DECLARE @p10 VarChar(1000) = '%dude%' DECLARE @p11 VarChar(1000) = '%dude%' DECLARE @p12 VarChar(1000) = '%dude%' DECLARE @p13 VarChar(1000) = '%dude%' DECLARE @p14 VarChar(1000) = '%dude%' DECLARE @p15 VarChar(1000) = '%dude%' DECLARE @p16 VarChar(1000) = '%dude%' DECLARE @p17 VarChar(1000) = '%dude%' DECLARE @p18 VarChar(1000) = '%dude%' DECLARE @p19 Int = 2 DECLARE @p20 Int = 10 DECLARE @p21 VarChar(1000) = '%dude%' DECLARE @p22 VarChar(1000) = 'CDN' DECLARE @p23 Int = 1 -- EndRegion SELECT [t10].[ITEMNO], [t10].[BASEDESCRIPTION], [t10].[value] AS [INFO], [t10].[UPC], [t10].[value2] AS [UNITPRICE], [t10].[CASEQTY], [t10].[value3] AS [QTY] FROM ( SELECT [t9].[ITEMNO], [t9].[BASEDESCRIPTION], [t9].[value], [t9].[UPC], [t9].[value2], [t9].[CASEQTY], [t9].[value3] FROM ( SELECT [t0].[ITEMNO], [t0].[BASEDESCRIPTION], [t2].[INFO] AS [value], [t0].[UPC], [t1].[UNITPRICE] AS [value2], [t0].[CASEQTY], [t5].[QTY] AS [value3], [t0].[ALLOWINBC], [t0].[ALLOWINAB], [t0].[DESCRIPTION], [t0].[CATEGORY], [t0].[FOODACCSPEC], [t0].[ITEMBRAND], [t0].[ITEMGROUP], [t0].[ITEMSUBTYPE], [t0].[ITEMTYPE], [t1].[CURRENCY], [t1].[DPRICETYPE], [t5].[ORDUNIQ], [t4].[ORDUNIQ] AS [ORDUNIQ2], [t1].[PRICELIST], [t8].[PRICLIST] FROM [ORDERFORMDUMP] AS [t0] LEFT OUTER JOIN [ICPRICP] AS [t1] ON REPLACE([t0].[ITEMNO], @p0, @p1) = [t1].[ITEMNO] LEFT OUTER JOIN [WEBITEMINFO] AS [t2] ON [t0].[ITEMNO] = [t2].[ITEMNO] LEFT OUTER JOIN ( SELECT [t3].[ORDUNIQ] FROM [WEBOEORDH] AS [t3] WHERE @p2 = [t3].[USER] ) AS [t4] ON 1=1 LEFT OUTER JOIN [WEBOEORDD] AS [t5] ON (REPLACE([t0].[ITEMNO], @p3, @p4) = [t5].[ITEMNO]) AND ([t4].[ORDUNIQ] = [t5].[ORDUNIQ]) LEFT OUTER JOIN ( SELECT [t6].[CUSTID] FROM [WEBLOGINACCESS] AS [t6] WHERE @p5 = [t6].[USER] ) AS [t7] ON 1=1 LEFT OUTER JOIN [ARCUS] AS [t8] ON [t7].[CUSTID] = [t8].[IDCUST] ) AS [t9] WHERE ([t9].[ALLOWINBC] = @p6) AND ([t9].[ALLOWINAB] = @p7) AND (([t9].[BASEDESCRIPTION] LIKE @p8) OR ([t9].[DESCRIPTION] LIKE @p9) OR ([t9].[CATEGORY] LIKE @p10) OR ([t9].[FOODACCSPEC] LIKE @p11) OR ([t9].[ITEMBRAND] LIKE @p12) OR ([t9].[ITEMGROUP] LIKE @p13) OR ([t9].[ITEMNO] LIKE @p14) OR ([t9].[ITEMSUBTYPE] LIKE @p15) OR ([t9].[ITEMTYPE] LIKE @p16) OR ([t9].[value] LIKE @p17) OR ([t9].[UPC] LIKE @p18) OR (SUBSTRING([t9].[UPC], @p19 + 1, @p20) LIKE @p21)) AND ([t9].[CURRENCY] = @p22) AND ([t9].[DPRICETYPE] = @p23) AND ([t9].[ORDUNIQ] <> [t9].[ORDUNIQ2]) AND ([t9].[PRICELIST] = [t9].[PRICLIST]) GROUP BY [t9].[ITEMNO], [t9].[BASEDESCRIPTION], [t9].[value], [t9].[UPC], [t9].[value2], [t9].[CASEQTY], [t9].[value3] ) AS [t10] ORDER BY [t10].[BASEDESCRIPTION] UPDATE As per HBomb's answer, I decided to create a stored procedure with parameters instead of doing multiple joins: CREATE PROCEDURE PRODUCT_PROCEDURE @USERID VARCHAR(MAX) AS BEGIN SELECT distinct datawarehouse.dbo.orderformdump.itemno, basedescription,info,upc,CAST((SELECT [UNITPRICE] FROM PPPLTD.dbo.[ICPRICP] WHERE [ITEMNO] = replace([DataWarehouse].[dbo].[ORDERFORMDUMP].[ITEMNO],'-','') AND [PRICELIST] = (select top 1 priclist from PPPLTD.dbo.ARCUS where IDCUST = (select top 1 CUSTID from PPPLTD.dbo.WEBLOGINACCESS where [USER] = @USERID)) and [CURRENCY] = 'CDN' and DPRICETYPE = 1) AS DECIMAL(18,2))as price,caseqty, qty AS userquantity FROM [DataWarehouse].[dbo].[ORDERFORMDUMP] LEFT JOIN pppltd.dbo.weboeordd ON pppltd.dbo.WEBOEORDD.ITEMNO = REPLACE(datawarehouse.dbo.ORDERFORMDUMP.ITEMNO,'-','') and orduniq not in (select orduniq from pppltd.dbo.weboeordsubmit) and WEBOEORDD.ORDUNIQ in (select orduniq from pppltd.dbo.weboeordh where [user] = @USERID) LEFT JOIN DATAWAREHOUSE.dbo.webiteminfo on webiteminfo.itemno = orderformdump.itemno where (allowinbc = 'Yes' or allowinab = 'Yes') order by BASEDESCRIPTION END Then I used Entity Framework's Database First approach to add my stored procedure and it has created a new DbContext with a method that sets the 'USERID' parameter in my stored procedure: public partial class DataWarehouseEntities : DbContext { public DataWarehouseEntities() : base("name=DataWarehouseEntities") { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { throw new UnintentionalCodeFirstException(); } public virtual ObjectResult<PRODUCT_PROCEDURE_Result> PRODUCT_PROCEDURE(string USERID) { var USERIDParameter = USERID != null ? new ObjectParameter("USERID", USERID) : new ObjectParameter("USERID", typeof(string)); return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<PRODUCT_PROCEDURE_Result>("PRODUCT_PROCEDURE", USERIDParameter); } } I've also tried: var USERIDParameter = USERID != null ? new SqlParameter("USERID", USERID) : new SqlParameter("USERID", typeof(string)); return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<PRODUCT_PROCEDURE‌​_Result>("PRODUCT_PROCEDURE @USERID", USERIDParameter); and finally, I tried running a much more simplified LINQ query on the results of my stored procedure: var products = db2.PRODUCT_PROCEDURE(username).Where (item => item.basedescription.Contains(searchword) || item.info.Contains(searchword) || item.itemno.Contains(searchword) || item.itemno.Contains(searchword.Replace("-", "")) || item.upc.Contains(searchword)); However, now I'm getting a NullReferenceException because the query isn't returning any results. Thank you.