1

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
Chris
  • 11
  • 2
  • 1
    possible duplicate of [How to do joins in LINQ on multiple fields in single join](http://stackoverflow.com/questions/373541/how-to-do-joins-in-linq-on-multiple-fields-in-single-join) – M.Babcock Mar 01 '12 at 18:47
  • Please do some search before ask questions. There is dozens of samples over net. – arunes Mar 01 '12 at 18:48
  • There are a lot of examples on the net. Unfortunately, those aren't working for me. It might have to do with comparing an int to a nullable int or a nullable int to an integer that is an int that is not a table field. But trust me, I did my research. – Chris Mar 01 '12 at 18:58

2 Answers2

0

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.

Community
  • 1
  • 1
kaj
  • 5,133
  • 2
  • 21
  • 18
  • Thanks for your comment. DefaultIfEmpty is great for doing a normal left/right join, but when I need table2 rows that are null or only have an id of 75, it still only returns the rows where table2 is not null. – Chris Mar 01 '12 at 20:13
0

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.

Chris
  • 11
  • 2