0

I'm trying to test out a dynamic join extension method as defined here to join products with categories in the Northwind SQL database. However I'm not 100% familiar with lambda expressions and fail to understand how to make the following SQL query using this extension method

select * from dbo.Products p
join dbo.Categories c on c.categoryID = p.CategoryID
where p.CategoryID =3 and UnitPrice > 3

Here is my dynamic linq query. 

var queryProducts = northwind.Products
 .Where("CategoryID = 3 AND UnitPrice > 3")
 .OrderBy("SupplierID");

var queryCategory = northwind.Categories
  .Where("CategoryID=3")
  .AsEnumerable();

var queryJoin = queryProducts.Join(queryCategory, "CategoryId", "CategoryId", "new(1 as abcd)",null);

This fails with exception: "An exception of type 'System.InvalidOperationException' occurred in System.Core.dll but was not handled in user code

Additional information: No method 'Join' on type 'System.Linq.Queryable' is compatible with the supplied arguments."

Any ideas on how to modify the Join above to make it work?

Community
  • 1
  • 1
JohanLarsson
  • 475
  • 1
  • 8
  • 23
  • I need to perform the join dynamically and only know which tables/columns to join on at run time, hence the need to use the extension method – JohanLarsson Jun 30 '14 at 10:25
  • I really can't see how that would work. The processor can't convert strings to table and column names. If there was a column UnitPrice in two of the tables, how would it know which table to check the value in? – Mikey Mouse Jun 30 '14 at 11:08
  • There must be some way to do the above using the extension method with aliases or similar – JohanLarsson Jun 30 '14 at 11:41
  • It looks like you can dynamically build up the where statement from a string, but I don't think you can name the tables as strings. If those three tables were foreign keyed it'd be easier – Mikey Mouse Jun 30 '14 at 12:25
  • This [link](http://stackoverflow.com/questions/13918534/multiple-table-join-in-linq-c-sharp-dynamically) names the tables explicitly, then lets you use a string to specify the where clause – Mikey Mouse Jun 30 '14 at 12:31
  • thanks mikey. Now I need to find the Join syntax. – JohanLarsson Jun 30 '14 at 13:01

0 Answers0