将SQL语句与多个左外联接和各个子句转换为LINQ

Question

我一直在尝试创建关联和导航属性，以简化我的linq查询。但是，由于与我合作的复杂，专有的数据库，这确实很困难。

在大多数此过程中，我一直在使用EF5上的数据库第一种方法。我无法首先使用数据库创建关联和导航属性，因此我尝试在SQL Server上创建外键，但在此方面没有成功。

对于下一步，我尝试使用代码第一种方法来创建表，关联和导航属性；但是，再次，由于我与之合作的数据库和服务器的必要性，我在简化查询方面没有成功。

我的最后一个手段只是在做我最初想做的事情。那就是获取此linq查询：

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;

获得与此SQL查询相同的结果：

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

当我在linqpad上执行LINQ时，它会产生此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

根据Hbomb的答案，我决定创建一个带有参数的存储过程，而不是进行多个加入：

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

然后，我使用Entity Framework的数据库第一种方法来添加我的存储过程，并且它创建了一个新的dbcontext，其方法在我的存储过程中设置了"用户ID"参数：

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);
        }
    }

我也尝试了：

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);

最后，我尝试在存储过程的结果上运行更简化的LINQ查询：

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));

但是，现在我得到了 nullReferenceException ，因为查询没有返回任何结果。

更新＃2

执行存储过程不会导致NullReferenceException。问题是linq查询。

我发现，当我单独使用var products = db2.PRODUCT_PROCEDURE(username).ToList()时，它会返回结果，但是一旦我尝试添加where子句，它就会返回null。

解决方案

在Hbomb的帮助下，我解决了这个问题。首先，在LINQ中创建多个加入，或创建关联和导航属性，在数据库中创建视图或存储过程更容易，然后使用该结果编写一个简单的LINQ查询（示例那是上面的）。

我发现我得到了我的 nullReferenceException ，因为我的info属性中数据库中的某些值是无效的。解决该问题我所要做的就是修改存储过程以将info列更改为isnull(info,'') as info。

。

最后，为了更好的搜索结果，我更改了查询：

var searchWords = searchword.ToLower().Split(' ');
            var products = db2.PRODUCT_PROCEDURE(username).ToList()
            .Where
                    (item => item.basedescription.ToLower().Contains(searchWords[0])
                     || item.info.ToLower().Contains(searchWords[0])
                     || item.itemno.Contains(searchword)
                     || item.itemno.Contains(searchword.Replace("-", ""))
                     || item.upc.Contains(searchword)
                     || (item.price.ToString() == searchword
                     && item.price.ToString() != null));
            if (searchWords.Length > 1)
            {
                for (int x = 0; x < searchWords.Length-1; x++)
                {
                    products = products.Where(i => i.basedescription.ToLower().Contains(searchWords[x]) || i.info.ToLower().Contains(searchWords[x]));
                }
            }

谢谢。

Answer 1

如果将SQL包裹在存储过程中，则可以使用EF调用该过程。这是一种方法：

 var searchParameter = new SqlParameter("@search", mySearchValue);
    this.Database.SqlQuery<YourEntityTypeForReturnVal>("MyProcedureName, @search", searchParameter);

您可以创建一个参数化存储过程作为SQL的包装器：

CREATE PROCEDURE MyProcedureName
    @search varchar(50)
AS
BEGIN
-- Copy and paste your existing Sql Query here, minus the variable declaration
END

有时候，我们可以做的最好的（也是最艰难的）事情是开发人员退后一步，问" 应该这样做"，而不是问" em>我是这样做的。"

Answer 2

我在战后到达，但是这是QueryFirst的工作吗？您可以按原样将SQL粘贴到查询模板中，但它保留在您的应用程序中（较容易的源控制，版本控制，维护）。生成的类使其可直接从您的应用程序代码中使用。