Looking to convert this Query to LINQ (no experience with this yet this will be my first time touching it):
SQL Query
/****** Lookup Option(s) For Trailer ******/
DECLARE @CompanyID nvarchar(6), @LookupTblID nvarchar(50), @MYPrefix nvarchar(3), @TableName nvarchar(47), @bDefaults bit;
SET @CompanyID = 'EZCUST';
SET @MYPrefix = '21-';
SET @bDefaults = 0; /* Only Defaults = 1 */
SET @TableName = '';
SET @LookupTblID = @MYPrefix+ ( CASE @TableName WHEN '' THEN '%' ELSE @TableName END );
/****** Lookup Default Option(s) For Trailer ******/
Select t1.[Trailer]
, t1.[Default]
, t1.[OptionDesc]
, t1.[OptionPart]
, t1.[OptionPrice]
, t1.[OptionWeight]
, t1.[OptionInst]
, t1.[OptionDwg]
, t1.[LookupTblID]
FROM (
SELECT
Min(CASE [ColName] WHEN 'Trailer' Then [DataValue] End) [Trailer]
,Min(CASE [ColName] WHEN 'Default' Then [DataValue] End) [Default]
,Min(CASE [ColName] WHEN 'OptionDesc' Then [DataValue] End) [OptionDesc]
,Min(CASE [ColName] WHEN 'OptionPart' Then [DataValue] End) [OptionPart]
,Min(CASE [ColName] WHEN 'OptionPrice' Then [DataValue] End) [OptionPrice]
,Min(CASE [ColName] WHEN 'OptionWeight' Then [DataValue] End) [OptionWeight]
,Min(CASE [ColName] WHEN 'OptionDwg' Then [DataValue] End) [OptionDwg]
,Min(CASE [ColName] WHEN 'OptionInst' Then [DataValue] End) [OptionInst]
,[RowNum]
,[LookupTblID]
,[Company]
FROM [EpicorERPTest].[Erp].[PcLookupTblValues]
WHERE [Company]=@CompanyID
AND [LookupTblID] LIKE @LookupTblID
Group By [Company], [LookupTblID], [RowNum]
) t1
WHERE (
( [Default] = 'True' )
OR
( @bDefaults = 0 AND [Default] = 'False' )
)
Order By [LookupTblID], [Trailer], [Default], [OptionPart]
The UD Method (equivalent) I’m attempting to replace (seems inefficient to me):
C# LINQ
var data = (
from t in Db.PcLookupTblValues
join d in Db.PcLookupTblValues
on new {t.Company, t.RowNum, t.LookupTblID}
equals new {d.Company, d.RowNum, d.LookupTblID}
join o in Db.PcLookupTblValues
on new {t.Company, t.RowNum, t.LookupTblID}
equals new {o.Company, o.RowNum, o.LookupTblID}
join r in Db.PcLookupTblValues
on new {t.Company, t.RowNum, t.LookupTblID}
equals new {r.Company, r.RowNum, r.LookupTblID}
join p in Db.PcLookupTblValues
on new {t.Company, t.RowNum, t.LookupTblID}
equals new {p.Company, p.RowNum, p.LookupTblID}
join w in Db.PcLookupTblValues
on new {t.Company, t.RowNum, t.LookupTblID}
equals new {w.Company, w.RowNum, w.LookupTblID}
join i in Db.PcLookupTblValues
on new {t.Company, t.RowNum, t.LookupTblID}
equals new {i.Company, i.RowNum, i.LookupTblID}
join g in Db.PcLookupTblValues
on new {t.Company, t.RowNum, t.LookupTblID}
equals new {g.Company, g.RowNum, g.LookupTblID}
where t.LookupTblID == MYPrefix + TableName
&& t.ColName == "Trailer"
&& t.DataValue == KNumber
&& d.ColName == "Default"
&& d.DataValue == "TRUE"
&& o.ColName == "OptionDesc"
&& r.ColName == "OptionPart"
&& p.ColName == "OptionPrice"
&& w.ColName == "OptionWeight"
&& i.ColName == "OptionInst"
&& g.ColName == "OptionDwg"
// orderby t.RowNum
select new {Desc = o.DataValue
, Part = r.DataValue
, Price = p.DataValue
, Weight= w.DataValue
, Inst = i.DataValue ?? ""
, Dwg = g.DataValue ?? ""
}
).ToList();
My attempt at evading LINQ based on: Raw SQL Queries
Entity Framework : Raw SQL Queries
var data = Context.Database.SqlQuery<string>(
" /****** Lookup Default Option(s) For Trailer ******/ " +
" SELECT " +
" Min(CASE [ColName] WHEN 'Trailer' Then DataValue End) Trailer " +
" ,Min(CASE [ColName] WHEN 'Default' Then DataValue End) [Default] " +
" ,Min(CASE [ColName] WHEN 'OptionPart' Then DataValue End) OptionPart " +
" ,Min(CASE [ColName] WHEN 'OptionDesc' Then DataValue End) OptionDesc " +
" ,Min(CASE [ColName] WHEN 'OptionPrice' Then DataValue End) OptionPrice " +
" ,Min(CASE [ColName] WHEN 'OptionWeight' Then DataValue End) OptionWeight " +
" ,Min(CASE [ColName] WHEN 'OptionDwg' Then DataValue End) OptionDwg " +
" ,Min(CASE [ColName] WHEN 'OptionInst' Then DataValue End) OptionInst " +
" ,[RowNum] " +
" ,[LookupTblID] " +
" ,[Company] " +
" FROM [PcLookupTblValues] " +
" WHERE Company='" + Context.CompanyID + "' " +
" AND LookupTblID = '" + MYPrefix + TableName + "'" +
" AND Trailer = '" + KNumber + "' " +
" AND [Default] = true " +
" Group By Company, LookupTblID, RowNum ").ToList();
Thanks 
