Link to home
Start Free TrialLog in
Avatar of ITMikeK
ITMikeKFlag for United States of America

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:

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    

Open in new window


I would like to translate this into a LINQ result like:

var joinResultRows = from PORel , etc.,..........  

Open in new window


so I can do loop through them to do something :
 foreach(var joinResult in joinResultRows)
 {
     string custID = joinResult.CustID;
     string ponum = joinResult.PONum;
 }    

Open in new window


Any thoughts? Thanks!
Avatar of ste5an
ste5an
Flag of Germany image

Why? Doing this in T-SQL is faster.

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.

SELECT POR.ReqNum ,
       POR.ReqLine ,
       XF.* -- Expand this..
FROM   E10DB.Erp.PORel POR
       INNER JOIN E10DB.Erp.ReqDetail RD ON RD.ReqNum = POR.ReqNum
       INNER JOIN E10DB.Ice.XFileAttch XF ON XF.Key1 = CONVERT(NVARCHAR(50), RD.ReqNum)
WHERE  POR.PONum = @ponum
       AND RD.Company = @company
       AND XF.Company = @company;

Open in new window

Avatar of ITMikeK

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.
Avatar of ITMikeK

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:

   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 };

Open in new window

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 -
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
                     };

Open in new window


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; }
    }
}

Open in new window

Produces the following output -User generated image
-saige-
Avatar of ITMikeK

ASKER

I'm trying to test it out now.  Where do the WHERE clause conditions fit?
Avatar of ITMikeK

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
                     });

Open in new window

Where clauses will normally appear before the select statement; 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
             where food.Name == "Beggin Strips"
             select new
             {
                 OwnerName = string.Format("{0} {1}", person.FirstName, person.LastName),
                 PetName = pet.Name,
                 Food = food.Name
             });

Open in new window

Produces the following output -User generated imageBe 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"));

Open in new window

Which produces the same results as above.

-saige-
Avatar of ITMikeK

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 :
INNER JOIN E10DB.Ice.XFileAttch XF ON XF.Key1 = CONVERT(NVARCHAR(50), RD.ReqNum)

Open in new window

Avatar of ITMikeK

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
                     });

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of it_saige
it_saige
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ITMikeK

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.