How do I do this?
I have this sql that I'd like to convert to linq.
SELECT "Delivery"."Promised_Date", "Delivery"."Job", Material_Location.Location_ID, "Delivery"."Shipped_Quantity", "Delivery"."Remaining_Quantity",
"Delivery"."Promised_Quantity", "Job"."Unit_Price", "Material_Location"."On_Hand_Qty", "Job"."Part_Number",
"Customer"."Name", "Customer"."Ship_Lead_Days", "User_Values"."Decimal1"
FROM ((("PRODUCTION"."dbo"."Delivery" "Delivery"
INNER JOIN "PRODUCTION"."dbo"."Job" "Job" ON "Delivery"."Job"="Job"."Job")
INNER JOIN "PRODUCTION"."dbo"."Customer" "Customer" ON "Job"."Customer"="Customer"."Customer")
LEFT OUTER JOIN "PRODUCTION"."dbo"."Material_Location" "Material_Location" ON "Job"."Part_Number"="Material_Location"."Material")
LEFT OUTER JOIN "PRODUCTION"."dbo"."User_Values" "User_Values" ON "Job"."User_Values"="User_Values"."User_Values"
WHERE "Delivery"."Promised_Date"<{?Select Promised Date1}
AND "Delivery"."Remaining_Quantity" > 0
and (coalesce(Material_Location.Location_ID,'') <> 'MSSICONSMT')
ORDER BY "Delivery"."Job", "Customer"."Name", "Delivery"."Promised_Date"
Working through the linq I have this so far;
var results = from d in Deliveries
join j in Jobs on d.Job equals j.Content
join c in Customers on j.Customer equals c.Content
join ml in Material_Locations on j.Part_Number equals ml.Material into t1
from t2 in t1.DefaultIfEmpty()
join uv in User_Values on (j.Content) equals (uv.Content) into t3
from t4 in t3.DefaultIfEmpty()
where d.Promised_Date <= Convert.ToDateTime("9/1/2017")
where d.Remaining_Quantity > 0
orderby d.Job, d.Promised_Date
select new
{
d.Promised_Date,
d.Job,
t2.Location_ID,
d.Shipped_Quantity,
d.Remaining_Quantity,
d.Promised_Quantity,
j.Unit_Price,
j.Part_Number,
c.Name,
c.Ship_Lead_Days
};
My problem comes linking the last two left outer joins. I have three tables that use the same name as the table for a key field. LinqPad changes these to be called Content. When joining the User_Values I should be saying t2.Job equals uv.User_Value, but instead I end up with t2.Content equals uv.Content. The t2.Content is both j.Job, ml.Material_Location, uv.User_Value, etc.
Is there any way to modify the "Content" name that LinqPad assigns to a field name? The data is coming from a SQL Server where I cannot change table structure.
Thanks,
Bernie
SELECT "Delivery"."Promised_Date", "Delivery"."Job", Material_Location.Location_ID, "Delivery"."Shipped_Quantity", "Delivery"."Remaining_Quantity",
"Delivery"."Promised_Quantity", "Job"."Unit_Price", "Material_Location"."On_Hand_Qty", "Job"."Part_Number",
"Customer"."Name", "Customer"."Ship_Lead_Days", "User_Values"."Decimal1"
FROM ((("PRODUCTION"."dbo"."Delivery" "Delivery"
INNER JOIN "PRODUCTION"."dbo"."Job" "Job" ON "Delivery"."Job"="Job"."Job")
INNER JOIN "PRODUCTION"."dbo"."Customer" "Customer" ON "Job"."Customer"="Customer"."Customer")
LEFT OUTER JOIN "PRODUCTION"."dbo"."Material_Location" "Material_Location" ON "Job"."Part_Number"="Material_Location"."Material")
LEFT OUTER JOIN "PRODUCTION"."dbo"."User_Values" "User_Values" ON "Job"."User_Values"="User_Values"."User_Values"
WHERE "Delivery"."Promised_Date"<{?Select Promised Date1}
AND "Delivery"."Remaining_Quantity" > 0
and (coalesce(Material_Location.Location_ID,'') <> 'MSSICONSMT')
ORDER BY "Delivery"."Job", "Customer"."Name", "Delivery"."Promised_Date"
Working through the linq I have this so far;
var results = from d in Deliveries
join j in Jobs on d.Job equals j.Content
join c in Customers on j.Customer equals c.Content
join ml in Material_Locations on j.Part_Number equals ml.Material into t1
from t2 in t1.DefaultIfEmpty()
join uv in User_Values on (j.Content) equals (uv.Content) into t3
from t4 in t3.DefaultIfEmpty()
where d.Promised_Date <= Convert.ToDateTime("9/1/2017")
where d.Remaining_Quantity > 0
orderby d.Job, d.Promised_Date
select new
{
d.Promised_Date,
d.Job,
t2.Location_ID,
d.Shipped_Quantity,
d.Remaining_Quantity,
d.Promised_Quantity,
j.Unit_Price,
j.Part_Number,
c.Name,
c.Ship_Lead_Days
};
My problem comes linking the last two left outer joins. I have three tables that use the same name as the table for a key field. LinqPad changes these to be called Content. When joining the User_Values I should be saying t2.Job equals uv.User_Value, but instead I end up with t2.Content equals uv.Content. The t2.Content is both j.Job, ml.Material_Location, uv.User_Value, etc.
Is there any way to modify the "Content" name that LinqPad assigns to a field name? The data is coming from a SQL Server where I cannot change table structure.
Thanks,
Bernie
Comments
http://www.linqpad.net/EntityFramework.aspx
For help with converting your query, I suggest asking this on Stack Overflow. But before doing so, read the following:
http://www.linqpad.net/WhyLINQBeatsSQL.aspx
It looks like you're "transliterating" a SQL query into LINQ, rather than writing it directly in LINQ and taking advantage of associations and shaped data. For an example of how to write a query directly in LINQ, see this answer:
https://stackoverflow.com/a/12240849/46223
If you still want to continue with a transliteration, you might be better off saving the trouble and sticking to SQL.