Home
Options

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

Comments

Sign In or Register to comment.