Convert Inner Join to LInq???
-
I'm having a hard time grasping the Linq syntax especially with queries. I can manage the simple queries but am still getting comfortable with them but I have a join to convert to Linq and it's killing me. Here is the query:
SELECT tbl_products.ProductName, tbl_xref_product_promo_code.Price
FROM tbl_promo_codes INNER JOIN
tbl_xref_product_promo_code ON tbl_promo_codes.IDPromoCode = tbl_xref_product_promo_code.IDPromoCode INNER JOIN
tbl_products ON tbl_xref_product_promo_code.IDPrice = tbl_products.ProductID
WHERE (tbl_promo_codes.IDPromoCode = 1)How do I recreate that in Linq? I can probably look at your code and figure it out but if you wanted to explain a few things let's start with this: (If you choose not to answer anything below totally fine. I really just need some help with the above query and that would be hugely appreciated.
from t1 in db.Table1
join t2 in db.Table2 on t1.field equals t2.field
select new { t1.field2, t2.field3}In the from t1 since the DB doesn't contain a t1 this must be an alias right and the same for t2? What is the select new verses just a select? Why new? Many thanks to any who reply. I really appreciate it.
-
I'm having a hard time grasping the Linq syntax especially with queries. I can manage the simple queries but am still getting comfortable with them but I have a join to convert to Linq and it's killing me. Here is the query:
SELECT tbl_products.ProductName, tbl_xref_product_promo_code.Price
FROM tbl_promo_codes INNER JOIN
tbl_xref_product_promo_code ON tbl_promo_codes.IDPromoCode = tbl_xref_product_promo_code.IDPromoCode INNER JOIN
tbl_products ON tbl_xref_product_promo_code.IDPrice = tbl_products.ProductID
WHERE (tbl_promo_codes.IDPromoCode = 1)How do I recreate that in Linq? I can probably look at your code and figure it out but if you wanted to explain a few things let's start with this: (If you choose not to answer anything below totally fine. I really just need some help with the above query and that would be hugely appreciated.
from t1 in db.Table1
join t2 in db.Table2 on t1.field equals t2.field
select new { t1.field2, t2.field3}In the from t1 since the DB doesn't contain a t1 this must be an alias right and the same for t2? What is the select new verses just a select? Why new? Many thanks to any who reply. I really appreciate it.
excuse my typing...my puppy is asleep in my one hand. breaking down the linq:
from t1 in context.Table1
t1 is an arbitrary variable name that linq binds to Table1. now when you use the variable you get intellisense on all fields in the table.
select new { t1.field2, t2.field3}
The select new is creating a new object that is a blend of fields from the two tables. You may want to see if there is a name parameter since you need that type name for enumeration. Now if you used a key in table 1 to get everything out of it's child table 2, then you could do this:
select t2;
The reason you don't need the new keyword is because t2 is a predefined variable.
modified on Thursday, February 19, 2009 4:20 PM
-
I'm having a hard time grasping the Linq syntax especially with queries. I can manage the simple queries but am still getting comfortable with them but I have a join to convert to Linq and it's killing me. Here is the query:
SELECT tbl_products.ProductName, tbl_xref_product_promo_code.Price
FROM tbl_promo_codes INNER JOIN
tbl_xref_product_promo_code ON tbl_promo_codes.IDPromoCode = tbl_xref_product_promo_code.IDPromoCode INNER JOIN
tbl_products ON tbl_xref_product_promo_code.IDPrice = tbl_products.ProductID
WHERE (tbl_promo_codes.IDPromoCode = 1)How do I recreate that in Linq? I can probably look at your code and figure it out but if you wanted to explain a few things let's start with this: (If you choose not to answer anything below totally fine. I really just need some help with the above query and that would be hugely appreciated.
from t1 in db.Table1
join t2 in db.Table2 on t1.field equals t2.field
select new { t1.field2, t2.field3}In the from t1 since the DB doesn't contain a t1 this must be an alias right and the same for t2? What is the select new verses just a select? Why new? Many thanks to any who reply. I really appreciate it.
I didn't test it so check it and please tell me if it's working as expected.
from promo_code in tbl_promo_codes // think of it like "foreach (var promo_code in tbl_promo_codes)"
where promo_code.IDPromoCode = 1 // i think it's better to write it here than any of the places labeled "//where" so you filter
before any joins but i might be wrongjoin xref_product_promo_code in tbl_xref_product_promo_code
on promo_code.IDPromoCode equals xref_product_promo_code.IDPromoCode
// specifying that you're doing join with another table. join must be followed by "on .. equals"//where
join product in tbl_products
on xref_product_promo_code.IDPrice equals product.ProductID//where
select new { product.ProductName, xref_product_promo_code.Price };
// returning an IEnumerable of a "new anonymous type" that contains two properties, ProductName and PriceEslam Afifi