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

  • edited June 14
    To customize the field names, you can connect to a typed data context that you've created in Visual Studio with LINQ to SQL or Entity Framework:
    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.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!