CODEWITHSUNDEEP
c#sql-serverlinq
KJSR
KJSR

Reputation: 1757

Converting SQL query to LINQ -- Operator '&&' cannot be applied to operands of type 'int' and 'bool'

I am having issue converting a SQL query into LINQ. I think I have 95% of it replicated into LINQ however having issue on a join

SQL

SELECT 
    table1.Column1 
FROM 
    table 1 table1 
LEFT OUTER JOIN 
    table 2 table2 ON table2.Column1 = table1.Column1 
                   AND table2.Column2 = 1838 
WHERE 
    table1.Column2 = 1 
    AND table1.Column3 = 24029 
    AND (table2.[Column3] IS NULL OR table2.[Column3] = 1)

LINQ so far

var query = from table1 in table 1
            join table2 in table 2 on table1.Column1 equals table2.Column1 && table2.Column2 == 1838 into result
            from table2 in result.DefaultIfEmpty()
            where table1.Column2 == 1 && table1.Column3 == 24029 && (table2.Column3 == null || table2.Column3 == 1)
            select table1.Column1;

When I run the query I get an error at this line

join table2 in table 2 on table1.Column1 equals table2.Column1 && tabl2.Column2 == 1838 into result

Error:

CS0019 Operator '&&' cannot be applied to operands of type 'int' and 'bool'

Any suggestions please?

Upvotes: 1

Views: 616

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205849

The LINQ syntax for composite key join is different. So instead of incorrect

join table2 in table_2 on table1.Column1 equals table2.Column1 && table2.Column2 == 1838 into result

you could use something like this

join table2 in table_2
on new { table1.Column1, Column2 = 1838 } equals new { table2.Column1, table2.Column2 }
into result

Upvotes: 4

juharr
juharr

Reputation: 32296

Linq is more strict than SQL when it comes to the on clause of a join. Basically it compares the stuff on one side of the equals to the other side. equals is not like == and you cannot chain more logic to the end with logical operators. The best approach is to just put the columns that form the relationship between the tables into the on clause of the join.

join table2 in table_2 on table1.Column1 equals table2.Column1 into result

Then move the table2.Column2 == 1838 to the where

where table2.Column2 == 1838
      && table1.Column2 == 1 
      && table1.Column3 == 24029 
      && (table2.Column3 == null || table2.Column3 == 1)

Alternatively this would also work, but really isn't best practice.

join table2 in table_2 
on new {table1.Column1, Column2 = 1838} 
equals new {table2.Column1, table2.Column2 } into result

As that is how you do multiple comparisons, but note it will only do equality unlike SQL that would allow you to do any type of logical clause.

Alternatively if you use EF and the tables have a foreign key relationship you should have navigation properties and you can do something like

from table1 in table_1
from table2 in table1.table_2s.DefaultIfEmpty()
....

Upvotes: 3

Related Questions