ITMikeK
asked on
Need help translating this multi-joined TSQL statement to LINQ
I looked at some of the other related questions, but didn't find a good answer. My TSQL query works fine:
I would like to translate this into a LINQ result like:
so I can do loop through them to do something :
Any thoughts? Thanks!
select PORel.ReqNum,PORel.ReqLine,XFiles.* from E10DB.Erp.PORel PORel
JOIN E10DB.Erp.ReqDetail ReqDetail ON ReqDetail.Company = @company AND
ReqDetail.ReqNum = PORel.ReqNum
JOIN E10DB.Ice.XFileAttch XFiles ON XFiles.Company = @company AND
XFiles.Key1 = CONVERT(nvarchar(50),ReqDetail.ReqNum)
WHERE PORel.PONum = @ponum
I would like to translate this into a LINQ result like:
var joinResultRows = from PORel , etc.,..........
so I can do loop through them to do something :
foreach(var joinResult in joinResultRows)
{
string custID = joinResult.CustID;
string ponum = joinResult.PONum;
}
Any thoughts? Thanks!
ASKER
I know, but we are confined to using the interface methods available within our ERP system, and it uses LINQ. BTW: Thanks for the SQL advice. I'm just doing rough cuts for a proof of concept, but those are all valid points that anyone reading this article should memorize.
ASKER
I've tried toying with it a bit and the code below returns a compile error : Invalid anonymous type member declarator.
Here's the code I tried:
Here's the code I tried:
var joinResultRows = from poRelRow in Db.PORel
join detailRow in Db.ReqDetail
on new { poRelRow.ReqNum } equals new { detailRow.ReqNum }
join xfRow in Db.XFileAttch
on new { detailRow.ReqNum.ToString() } equals new { xfRow.Key1 }
select new {poRelRow.ReqNum, poRelRow.ReqLine, xfRow.DrawDesc, xfRow.XFileRefNum, xfRow.FileName, xfRow.DocTypeID };
Every time you use new without a type, you are creating an anonymous type. By your query it looks like you just need to do the following -
For example -
-saige-
var joinResultRows = from poRelRow in Db.PORel
join detailRow in Db.ReqDetail on poRelRow.ReqNum equals detailRow.ReqNum
join xfRow in Db.XFileAttch on detailRow.ReqNum equals xfRow.Key1
select new
{
poRelRow.ReqNum,
poRelRow.ReqLine,
xfRow.DrawDesc,
xfRow.XFileRefNum,
xfRow.FileName,
xfRow.DocTypeID
};
For example -
using System;
using System.Collections.Generic;
using System.Linq;
namespace EE_Q29040837
{
class Program
{
static readonly List<Person> people = new List<Person>();
static readonly List<Pet> pets = new List<Pet>();
static readonly List<Food> foods = new List<Food>();
static void Main(string[] args)
{
people.AddRange(new Person[] {
new Person { ID = 0, FirstName = "Magnus", LastName = "Hedlund" },
new Person { ID = 1, FirstName = "Terry", LastName = "Adams" },
new Person { ID = 2, FirstName = "Charlotte", LastName = "Weiss" },
new Person { ID = 3, FirstName = "Arlene", LastName = "Huff" },
new Person { ID = 4, FirstName = "Rui", LastName = "Raposo" }
});
pets.AddRange(new Pet[] {
new Pet { ID = 0, Name = "Barley", OwnerID = 1 },
new Pet { ID = 1, Name = "Boots", OwnerID = 1 },
new Pet { ID = 2, Name = "Whiskers", OwnerID = 2 },
new Pet { ID = 3, Name = "Blue Moon", OwnerID = 4 },
new Pet { ID = 4, Name = "Daisy", OwnerID = 0 },
});
foods.AddRange(new Food[]
{
new Food { Name = "Puppy Chow", PetID = 3 },
new Food { Name = "Puppy Chow", PetID = 4 },
new Food { Name = "Fancy Feast", PetID = 1 },
new Food { Name = "Fancy Feast", PetID = 2 },
new Food { Name = "Purina", PetID = 3 },
new Food { Name = "Beggin Strips", PetID = 3 },
new Food { Name = "Beggin Strips", PetID = 4 },
new Food { Name = "Tuna", PetID = 2 },
new Food { Name = "Makeral", PetID = 1 },
});
var union = (from person in people
join pet in pets on person.ID equals pet.OwnerID
join food in foods on pet.ID equals food.PetID
select new
{
OwnerName = string.Format("{0} {1}", person.FirstName, person.LastName),
PetName = pet.Name,
Food = food.Name
});
foreach (var row in union)
Console.WriteLine(row);
Console.ReadLine();
}
}
class Person
{
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
class Pet
{
public int ID { get; set; }
public int OwnerID { get; set; }
public string Name { get; set; }
}
class Food
{
public int PetID { get; set; }
public string Name { get; set; }
}
}
Produces the following output --saige-
ASKER
I'm trying to test it out now. Where do the WHERE clause conditions fit?
ASKER
This makes it through Intellisense, but I'm still trying to add the WHERE conditions:
var joinResults = (from poRelRow in Db.PORels
join detailRow in Db.ReqDetails on poRelRow.ReqNum equals detailRow.ReqNum
join xfRow in Db.XFileAttches on detailRow.ReqNum.ToString() equals xfRow.Key1
join xdRow in Db.XFileRefs on xfRow.XFileRefNum equals xdRow.XFileRefNum
select new
{
poRelRow.ReqNum,
poRelRow.ReqLine,
xdRow.XFileRefNum,
xdRow.XFileDesc,
xdRow.XFileName,
xdRow.DocTypeID
});
Where clauses will normally appear before the select statement; e.g. -
-saige-
var union = (from person in people
join pet in pets on person.ID equals pet.OwnerID
join food in foods on pet.ID equals food.PetID
where food.Name == "Beggin Strips"
select new
{
OwnerName = string.Format("{0} {1}", person.FirstName, person.LastName),
PetName = pet.Name,
Food = food.Name
});
Produces the following output -Be warned, though, LINQ to Entities is a similar but different animal. You must remember that you may not be able to perform specific .NET functions within the query body. This is because LINQ to Entities, converts the query body into a command tree query for T-SQL. All this means is that you will need to perform your functional where on the query results; e.g. -
var union = (from person in people
join pet in pets on person.ID equals pet.OwnerID
join food in foods on pet.ID equals food.PetID
select new
{
OwnerName = string.Format("{0} {1}", person.FirstName, person.LastName),
PetName = pet.Name,
Food = food.Name
});
union = union.Where(d => d.Food.Equals("Beggin Strips"));
Which produces the same results as above.-saige-
ASKER
Saige,
I think I'm almost there. How do I handle the type conversion in one of the joins? I'm getting a LINQ to Entities does not recognize the method ToString.........
It would be for this condition :
I think I'm almost there. How do I handle the type conversion in one of the joins? I'm getting a LINQ to Entities does not recognize the method ToString.........
It would be for this condition :
INNER JOIN E10DB.Ice.XFileAttch XF ON XF.Key1 = CONVERT(NVARCHAR(50), RD.ReqNum)
ASKER
The most completed version I've come up with is listed below. Still working on that int to string/string to int conversion.
var joinResultRows = (from poRelRow in Db.PORel
join detailRow in Db.ReqDetail on poRelRow.ReqNum equals detailRow.ReqNum
join xfRow in Db.XFileAttch on detailRow.ReqNum equals Convert.ToInt32(xfRow.Key1)
join xdRow in Db.XFileRef on xfRow.XFileRefNum equals xdRow.XFileRefNum
where poRelRow.PONum.Equals(strPO)
select new
{
poRelRow.ReqNum,
poRelRow.ReqLine,
xdRow.XFileRefNum,
xdRow.XFileDesc,
xdRow.XFileName,
xdRow.DocTypeID
});
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
While it did not fix my immediate issue (that's due to the software customization limits of our ERP system), the rest was very educational and helpful. Very concise and detailed explanations were provided.
Just some comments:
- Always use full syntax, favor INNER JOIN over JOIN.
- Don't the asterisk in production code.
- Why using alias names when they are the same as the table name?
- Place "normal" filter predicates into a WHERE clause, not the JOIN clause.
E.g.
Open in new window