locked
How to convert this SQL query to LINQ? RRS feed

  • Question

  • User-1651604128 posted

    Here is the SQL query codes:

    SELECT tbl_AIG_Dept_PLA.PLA_ID, tbl_AIG_Dept_PLA.Department_ID, tbl_PLAIN_LANGUAGE_ADDRESS.PLA_NAME,
    tbl_Department.D_ID, tbl_Department.Section_ID
    FROM tbl_PLAIN_LANGUAGE_ADDRESS INNER JOIN(tbl_Department RIGHT JOIN tbl_AIG_Dept_PLA
    ON tbl_Department.Department_ID = tbl_AIG_Dept_PLA.Department_ID) ON
    tbl_PLAIN_LANGUAGE_ADDRESS.PLA_ID = tbl_AIG_Dept_PLA.PLA_ID
    WHERE(tbl_AIG_Dept_PLA.AIG_ID = 431) AND tbl_AIG_Dept_PLA.Distribution_Code = 'A'
    ORDER BY tbl_PLAIN_LANGUAGE_ADDRESS.PLA_NAME, tbl_Department.D_ID

    I can not figure out the code inside of INNER JOIN, I appreciate if anybody can help me out, much appreciated!!

    Wednesday, June 13, 2018 1:57 PM

All replies

  • User1520731567 posted

    Hi Peter Cong,

    According to your requirement,I make a demo, you could refer to it:

      var partialResult5 = (from Table2  in db.Table2
                                      join InnerJoin in( 
                                       from Table3 in db.Table3
                                       join Table1 in db.Table1
                                       on Table3.Id equals Table1.Id into leftJoin
                                       from j in leftJoin.DefaultIfEmpty()
                                       select new
                                       {
                                           CID = j.Id, //data from Table1
                                           AID = Table3.Id, //data from Table3
                                           ACC = Table3.Name,
                                           SIZE = j.size,
                                           NAME = j.country
                                       }                                
                                      )
                                      on Table2.Id equals InnerJoin.AID
                                      where InnerJoin.AID > 1 && InnerJoin.ACC =="aaa"  
                                      orderby Table2.Id, InnerJoin.AID
                                      select new
                                      {
                                          Table2.Id, //data from Table2
                                          InnerJoin.NAME, //data from Table1
                                          InnerJoin.AID //data from Table3
    
                                      }).ToList();

    from Table3 in db.Table3

    join Table1 in db.Table1

    on Table3.Id equals Table1.Id into leftJoin

    from j in leftJoin.DefaultIfEmpty()

    select new

    {

    CID = j.Id,

    AID = Table3.Id,

    ACC = Table3.Name,

    SIZE = j.size,

    NAME = j.country

    }

    I suggest use the DefaultIfEmpty method on the results of a group join to perform a right outer join.

    Actually,this part of code i post here can be said both left and right join.

    For AAA and BBB, it's left join.

    For BBB and AAA, it's right join.

    There is no obvious division between them.

    In addition, I use inner join nest right join, and use where ,orderby to filter data ,it meet your need.

    Best Regards.
     
    Yuki Tao

    Thursday, June 14, 2018 10:47 AM
  • User-1651604128 posted

    Hi Peter Cong,

    According to your requirement,I make a demo, you could refer to it:

      var partialResult5 = (from Table2  in db.Table2
                                      join InnerJoin in( 
                                       from Table3 in db.Table3
                                       join Table1 in db.Table1
                                       on Table3.Id equals Table1.Id into leftJoin
                                       from j in leftJoin.DefaultIfEmpty()
                                       select new
                                       {
                                           CID = j.Id, //data from Table1
                                           AID = Table3.Id, //data from Table3
                                           ACC = Table3.Name,
                                           SIZE = j.size,
                                           NAME = j.country
                                       }                                
                                      )
                                      on Table2.Id equals InnerJoin.AID
                                      where InnerJoin.AID > 1 && InnerJoin.ACC =="aaa"  
                                      orderby Table2.Id, InnerJoin.AID
                                      select new
                                      {
                                          Table2.Id, //data from Table2
                                          InnerJoin.NAME, //data from Table1
                                          InnerJoin.AID //data from Table3
    
                                      }).ToList();

    Yuki Tao

    from Table3 in db.Table3

    join Table1 in db.Table1

    on Table3.Id equals Table1.Id into leftJoin

    from j in leftJoin.DefaultIfEmpty()

    select new

    {

    CID = j.Id,

    AID = Table3.Id,

    ACC = Table3.Name,

    SIZE = j.size,

    NAME = j.country

    }

    I suggest use the DefaultIfEmpty method on the results of a group join to perform a right outer join.

    Actually,this part of code i post here can be said both left and right join.

    For AAA and BBB, it's left join.

    For BBB and AAA, it's right join.

    There is no obvious division between them.

    In addition, I use inner join nest right join, and use where ,orderby to filter data ,it meet your need.

    Best Regards.
     
    Yuki Tao

    Hi Yuki, thank you so much for your help, plus you details explanation, much appreciated !!!

    But when I implemented your codes, I can not implement this line of your code:

    where InnerJoin.AID > 1 && InnerJoin.ACC == "aaa"

    If you check my original post, I need this:

    WHERE(tbl_AIG_Dept_PLA.AIG_ID = 431) AND tbl_AIG_Dept_PLA.Distribution_Code = 'A'

    so compare to your code, the above line code should be converted to:

    Where(Table1.AIG_ID == 431 && Table1.Distribution_Code equals('A'),

    but if I replace this code to your code, the Table1 here is not compatible.

    Any idea to make it work? thanks a lot again 

    Thursday, June 14, 2018 12:16 PM
  • User1520731567 posted

    Hi Peter Cong,

    I'm sorry for the late.
     

    If you check my original post, I need this:

    WHERE(tbl_AIG_Dept_PLA.AIG_ID = 431) AND tbl_AIG_Dept_PLA.Distribution_Code = 'A'

    so compare to your code, the above line code should be converted to:

    Where(Table1.AIG_ID == 431 && Table1.Distribution_Code equals('A'),

    but if I replace this code to your code, the Table1 here is not compatible.

     
    If you want to filter data by tbl_AIG_Dept_PLA.AIG_ID and tbl_AIG_Dept_PLA.Distribution_Code.

    As I can see in your code, tbl_AIG_Dept_PLA is the right table in right join.

    I suggest you could modify the code:

    Where(Table1.AIG_ID == 431 && Table1.Distribution_Code equals('A')

    to:

    where InnerJoin.AIG_ID ==431 && InnerJoin.Distribution_Code ==’A’

    Note that: fileds named AIG_ID and Distribution_Code must be defined in the right join.

    For example:

    from Table3 in db.AuthorModels
                                       join Table1 in db.CountrySizes
                                       on Table3.Id equals Table1.Id into leftJoin
                                       from j in leftJoin.DefaultIfEmpty()
                                       select new
                                       {
                                           CID = j.Id,
                                           AID = Table3.Id,
                                           ACC = Table3.Name,
                                           SIZE = j.size,
                                           NAME = j.country,
                                          AIG_ID=j. AIG_ID,
                                          Distribution_Code=j. Distribution_Code
                                       }
    

    Best Regards.
     
    Yuki Tao

    Friday, June 15, 2018 9:17 AM
  • User-1651604128 posted

    Hi Peter Cong,

    I'm sorry for the late.
     

    Peter Cong

    If you check my original post, I need this:

    WHERE(tbl_AIG_Dept_PLA.AIG_ID = 431) AND tbl_AIG_Dept_PLA.Distribution_Code = 'A'

    so compare to your code, the above line code should be converted to:

    Where(Table1.AIG_ID == 431 && Table1.Distribution_Code equals('A'),

    but if I replace this code to your code, the Table1 here is not compatible.

     
    If you want to filter data by tbl_AIG_Dept_PLA.AIG_ID and tbl_AIG_Dept_PLA.Distribution_Code.

    As I can see in your code, tbl_AIG_Dept_PLA is the right table in right join.

    I suggest you could modify the code:

    Where(Table1.AIG_ID == 431 && Table1.Distribution_Code equals('A')

    to:

    where InnerJoin.AIG_ID ==431 && InnerJoin.Distribution_Code ==’A

    Note that: fileds named AIG_ID and Distribution_Code must be defined in the right join.

    For example:

    from Table3 in db.AuthorModels
                                       join Table1 in db.CountrySizes
                                       on Table3.Id equals Table1.Id into leftJoin
                                       from j in leftJoin.DefaultIfEmpty()
                                       select new
                                       {
                                           CID = j.Id,
                                           AID = Table3.Id,
                                           ACC = Table3.Name,
                                           SIZE = j.size,
                                           NAME = j.country,
                                          AIG_ID=j. AIG_ID,
                                          Distribution_Code=j. Distribution_Code
                                       }

    Best Regards.
     
    Yuki Tao

    Thank you so much, Yuki, I will try it next Monday when I back to my office and let you know then.

    Have a great weekend!

    Friday, June 15, 2018 9:24 PM
  • User-1651604128 posted

    Hi Peter Cong,

    I'm sorry for the late.
     

    Peter Cong

    If you check my original post, I need this:

    WHERE(tbl_AIG_Dept_PLA.AIG_ID = 431) AND tbl_AIG_Dept_PLA.Distribution_Code = 'A'

    so compare to your code, the above line code should be converted to:

    Where(Table1.AIG_ID == 431 && Table1.Distribution_Code equals('A'),

    but if I replace this code to your code, the Table1 here is not compatible.

     
    If you want to filter data by tbl_AIG_Dept_PLA.AIG_ID and tbl_AIG_Dept_PLA.Distribution_Code.

    As I can see in your code, tbl_AIG_Dept_PLA is the right table in right join.

    I suggest you could modify the code:

    Where(Table1.AIG_ID == 431 && Table1.Distribution_Code equals('A')

    to:

    where InnerJoin.AIG_ID ==431 && InnerJoin.Distribution_Code ==’A

    Note that: fileds named AIG_ID and Distribution_Code must be defined in the right join.

    For example:

    from Table3 in db.AuthorModels
                                       join Table1 in db.CountrySizes
                                       on Table3.Id equals Table1.Id into leftJoin
                                       from j in leftJoin.DefaultIfEmpty()
                                       select new
                                       {
                                           CID = j.Id,
                                           AID = Table3.Id,
                                           ACC = Table3.Name,
                                           SIZE = j.size,
                                           NAME = j.country,
                                          AIG_ID=j. AIG_ID,
                                          Distribution_Code=j. Distribution_Code
                                       }

    Best Regards.
     
    Yuki Tao

    Hi Yuki, here is my implementation of your codes:

     var partialResult5 = (from Table2 in db.tbl_PLAIN_LANGUAGE_ADDRESS
                                      join InnerJoin in (
                                       from Table3 in db.tbl_DEPARTMENT
                                       join Table1 in db.tbl_AIG_DEPT_PLA
                                       on Table3.DEPARTMENT_ID equals Table1.DEPARTMENT_ID into leftJoin
                                       from j in leftJoin.DefaultIfEmpty()
                                       select new
                                       {
                                           CID = j.PLA_ID, //data from Table1
                                           AID = Table3.DEPARTMENT_ID, //data from Table3
                                           ACC = Table3.DND_ID,
                                           SECTION_ID = Table3.SECTION_ID,
                                           AIG_ID = j.AIG_ID,
                                           Distribution_Code = j.DISTRIBUTION_CODE
                                          
                                       }
                                      )
                                      on Table2.PLA_ID equals InnerJoin.CID
                                      where InnerJoin.AIG_ID == 431 && InnerJoin.Distribution_Code.Equals('A')
                                      orderby Table2.PLA_NAME, InnerJoin.AID
                                      select new
                                      {
                                          Table2.PLA_NAME, //data from Table2  = tbl_PLAIN_LANGUAGE_ADDRESS.PLA_NAME
                                          InnerJoin.CID, //data from Table1   = tbl_AIG_Dept_PLA.PLA_ID
                                          InnerJoin.SECTION_ID //data from Table3   = tbl_Department.Section_ID                              
    
                                      }).ToList();

    There is no compile time error this time, but I got run time error of:

    {"Unable to create a constant value of type 'System.Object'. Only primitive types or enumeration types are supported in this context."}

    Any idea how to fix it? thanks again

    Monday, June 18, 2018 4:54 PM
  • User1520731567 posted

    Hi Peter Cong,

    Where(Table1.AIG_ID == 431 && Table1.Distribution_Code equals('A')

    I suggest you could use == instead of equals().

    You could refer to this line that I post before:

    where InnerJoin.AIG_ID ==431 && InnerJoin.Distribution_Code ==’A’

    And you could refer to this link:

    https://stackoverflow.com/questions/4592432/linq-query-keeps-throwing-unable-to-create-a-constant-value-of-type-system-obje

    Best Regards.

    Yuki Tao

    Tuesday, June 19, 2018 2:38 AM
  • User-1651604128 posted

    Hi Peter Cong,

    Peter Cong

    Where(Table1.AIG_ID == 431 && Table1.Distribution_Code equals('A')

    I suggest you could use == instead of equals().

    You could refer to this line that I post before:

    where InnerJoin.AIG_ID ==431 && InnerJoin.Distribution_Code ==’A’

    And you could refer to this link:

    https://stackoverflow.com/questions/4592432/linq-query-keeps-throwing-unable-to-create-a-constant-value-of-type-system-obje

    Best Regards.

    Yuki Tao

    Hi Yuki, Thank you for your quick response and appreciate your help again.

    No the "==" does not work in this case, I changed it to equals because I tried to use "==" and got compile time error: "Operator '==' can not be applied to operands of type 'method group' and 'char' 

    Please let me know if you have any better idea, thanks again.

    Tuesday, June 19, 2018 6:05 PM
  • User1520731567 posted

    Hi Peter Cong,

    I'm sorry I can't reproduce your issue.

    Because it works well in my project.

    Could you please post more details codes about how you define the ef model?

    Such as: Your entities about three tables.

    Best Regards.

    Wednesday, June 20, 2018 6:26 AM