I am trying to convert this 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
to LINQ:
from item in db.OrderFormDump
join icp in db.IcPricP on item.ITEMNO.Replace("-", "") equals icp.ITEMNO into icpGroup
from iG in icpGroup.DefaultIfEmpty()
join itemInfo in db.WebItemInfo on item.ITEMNO equals itemInfo.ITEMNO into itemInfoGroup
from iIG in itemInfoGroup.DefaultIfEmpty()
join weboeordh in db.WebOEOrdH on "brian" equals weboeordh.USER into weboeordhGroup
from wOEODHG in weboeordhGroup.DefaultIfEmpty()
join weboeordd in db.WebOEOrdD on new { itemno = item.ITEMNO.Replace("-", ""), orduniq = wOEODHG.ORDUNIQ } equals new { itemno = weboeordd.ITEMNO, orduniq = weboeordd.ORDUNIQ } into weboeorddGroup
from wOEODG in weboeorddGroup.DefaultIfEmpty()
join weboeordsubmit in db.WebOEOrdSubmit on wOEODG.ORDUNIQ equals weboeordsubmit.ORDUNIQ into weboeordsubmitGroup
from wOEOSG in weboeordsubmitGroup.DefaultIfEmpty()
join webloginaccess in db.WebLoginAccess on "brian" equals webloginaccess.USER into webloginaccessGroup
from wLAG in webloginaccessGroup.DefaultIfEmpty()
join arcus in db.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
I'm not getting the same results in LINQ as I am in SQL. The SQL statement gives me the correct results.
I am really close, I just need to tweak the LINQ a little to get the correct results.
Thank you.
UPDATE
When I use LINQPad, it produces this SQL from my LINQ query:
-- Region Parameters
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 [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], [t9].[PRICLIST], [t8].[CUSTID], [t9].[IDCUST]
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 [WEBOEORDSUBMIT] AS [t6] ON [t5].[ORDUNIQ] = [t6].[ORDUNIQ]
LEFT OUTER JOIN (
SELECT [t7].[CUSTID]
FROM [WEBLOGINACCESS] AS [t7]
WHERE @p5 = [t7].[USER]
) AS [t8] ON 1=1
CROSS JOIN [ARCUS] AS [t9]
) AS [t10]
WHERE ([t10].[ALLOWINBC] = @p6) AND ([t10].[ALLOWINAB] = @p7) AND (([t10].[BASEDESCRIPTION] LIKE @p8) OR ([t10].[DESCRIPTION] LIKE @p9) OR ([t10].[CATEGORY] LIKE @p10) OR ([t10].[FOODACCSPEC] LIKE @p11) OR ([t10].[ITEMBRAND] LIKE @p12) OR ([t10].[ITEMGROUP] LIKE @p13) OR ([t10].[ITEMNO] LIKE @p14) OR ([t10].[ITEMSUBTYPE] LIKE @p15) OR ([t10].[ITEMTYPE] LIKE @p16) OR ([t10].[value] LIKE @p17) OR ([t10].[UPC] LIKE @p18) OR (SUBSTRING([t10].[UPC], @p19 + 1, @p20) LIKE @p21)) AND ([t10].[CURRENCY] = @p22) AND ([t10].[DPRICETYPE] = @p23) AND ([t10].[ORDUNIQ] <> [t10].[ORDUNIQ2]) AND ([t10].[PRICELIST] = [t10].[PRICLIST]) AND ([t10].[CUSTID] = [t10].[IDCUST])
GROUP BY [t10].[ITEMNO], [t10].[BASEDESCRIPTION], [t10].[value], [t10].[UPC], [t10].[value2], [t10].[CASEQTY], [t10].[value3]
UPDATE #2
I've tried adding navigation properties to my ORDERFORMDUMP table and my LINQ query is now significantly smaller and simpler.
Here's my new LINQ:
var query = from item in db.OrderFormDump
where (item.ALLOWINBC == "Yes" && item.ALLOWINAB == "Yes")
&& item.ICPRICP.ITEMNO == item.NODASHITEMNO
&& item.WEBITEMINFO.ITEMNO == item.ITEMNO
&& item.WEBOEORDH.USER == username
&& item.WEBOEORDD.ITEMNO == item.NODASHITEMNO
&& item.WEBOEORDD.ORDUNIQ == item.WEBOEORDH.ORDUNIQ
&& item.WEBOEORDD.ORDUNIQ != item.WEBOEORDSUBMIT.ORDUNIQ
&& item.WEBLOGINACCESS.USER == username
&& item.ICPRICP.PRICELIST == item.ARCUS.PRICLIST
&& item.ICPRICP.CURRENCY == "CDN"
&& item.ICPRICP.DPRICETYPE == 1
&& (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") || item.WEBITEMINFO.INFO.Contains("dude") || item.UPC.Contains("dude") || item.UPC.Substring(2, 10).Contains("dude"))
orderby item.BASEDESCRIPTION
select new { item.ITEMNO, item.BASEDESCRIPTION, item.WEBITEMINFO.INFO, item.UPC, item.ICPRICP.UNITPRICE, item.CASEQTY, item.WEBOEORDD.QTY };
and here's my Model:
public partial class ORDERFORMDUMP
{
public string ITEMNO { get; set; }
public string DESCRIPTION { get; set; }
public string UPC { get; set; }
public Nullable<decimal> BASEPRICE { get; set; }
public string CATCODE { get; set; }
public string CATEGORY { get; set; }
public string SUBCAT { get; set; }
public string CATSORT { get; set; }
public string ITEMGROUP { get; set; }
public string ITEMTYPE { get; set; }
public string ITEMBRAND { get; set; }
public Nullable<int> CASEQTY { get; set; }
public Nullable<decimal> SUGRETAIL { get; set; }
public string ALLOWINBC { get; set; }
public string ALLOWINAB { get; set; }
public string ALLOWINPLANET { get; set; }
public string ALLOWINTISOL { get; set; }
public string ALLOWINPETLAND { get; set; }
public string FOODACCSPEC { get; set; }
public string ITEMBRANDSORT { get; set; }
public string ITEMNAME { get; set; }
public string ALLOWINBCTBD { get; set; }
public string ALLOWINABTBD { get; set; }
public string VENDORID { get; set; }
public string BASEDESCRIPTION { get; set; }
public string ITEMSUBTYPE { get; set; }
public virtual ICPRICP ICPRICP { get; set; }
public virtual WEBITEMINFO WEBITEMINFO { get; set; }
public virtual WEBOEORDH WEBOEORDH { get; set; }
public virtual WEBOEORDD WEBOEORDD { get; set; }
public virtual WEBOEORDSUBMIT WEBOEORDSUBMIT { get; set; }
public virtual WEBLOGINACCESS WEBLOGINACCESS { get; set; }
public virtual ARCUS ARCUS { get; set; }
[NotMapped]
public string NODASHITEMNO
{
get { return ITEMNO.Replace("-",""); }
}
}
However, now I get the error:
The specified type member 'ICPRICP' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.
Aucun commentaire:
Enregistrer un commentaire