CODEWITHSUNDEEP
c#linqsql-server-2008
Chris
Chris

Reputation: 11

SQL to Linq Left Join with 'AND' in the SQL Join Statement

I have a SQL query that works perfectly that I need to convert to Linq. I need to return all the records one table and join it to a second table. I need to return all of the results from the first table joined with results from the second table where the value of a specific field in the second table equals a variable value (75 in the example below) or returns null.

So the total number of rows in the result should be the total number of rows from table1. The part of the row from the join from table2 should either show values from table2 where a record existed with a value of 75 or null where the record doesn't exist.

EDIT: I should mention that t1.field1 is an int and t2.field1 is a nullable int.

I tried multiple linq statements, grouping joins, asking coworkers, and googling til my eyes bleed. I'm stuck. I realize my question wording may not be clear, and I apologize in advance if it isn't.

Thanks in advance. Chris

The SQL Query:

SELECT *
FROM table1 AS t1 LEFT OUTER JOIN 
table2 AS t2 ON t1.field1 = t2.field1 AND t2.field2 = 75

Upvotes: 1

Views: 447

Answers (2)

Chris
Chris

Reputation: 11

Thank you for your responses. Because I need some nulls, but only the ones where the specific id is null and not all values that are null, none of the solutions above will work. It seems that to do this in linq will be very tough if it is possible.

So, in the interest of time, I decided to take the SQL query that worked and turn it into a stored procedure and function import. I feel like that's probably not the right way to do it, but time is always a factor.

Thanks again for your responses.

Upvotes: 0

kaj
kaj

Reputation: 5251

Use DefaultIfEmpty - see LINQ - Left Join, Group By, and Count and http://msdn.microsoft.com/en-us/library/bb397895.aspx for samples of how to achieve this

An answer that works but isn't as elegant as I'd expected: 

var q = from item1 in table1  
            join item2 in table2 on new { Field1=(int?)item1.Field1, Field2=75 }  
                   equals new { item2.Field1, item2.Field2 } into gj  
            from subItem2 in gj.DefaultIfEmpty()  
            select new { F1= item1.Field1, F2 = ( subItem2 == null ? string.Empty : subItem2.Field2.ToString()) };

I couldn't see where to fit the where clause in (Field2 = 75) so instead went with the composite key between the tables to achieve the same effect.

Second bit of ugliness is the cast to the nullable int because or Field1 in the composite key so it can be equated to the corresponding field on table 2.

Obviously you return in the anonymous type whatever values you're interested in. Note you can't return null which is why I showed a string representation of Field2 just so you can see what is returned from the second table.

Upvotes: 0

Related Questions