How to convert this SQL query to LINQ?

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!!

Answer 1

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

Answer 2

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 

Answer 3

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

Answer 4

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!

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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.