Join multiple table using generic repository patten with Entity framework with unit of work

kannan Eswar picture kannan Eswar · Mar 12, 2014 · Viewed 14.4k times · Source

I am developing a web application using MVC4 with Entity framework 5.

I have created generic repository for accessing database with unit of work.

Having the below two repository,

  • CustomerRepository - Customer table
  • LibraryRepository - Library table

These two tables are not linked with each other( there is no foreign key relation ).

Want to write a query by combining these repository. Is possible to write a query by combining two different repository?

If yes, please let me know the way.

Answer

DanS picture DanS · Mar 12, 2014

If your generic repositories expose an IQueryable method, you should be able to use a LINQ join to query both repositories:

var items = from c in customerRepository.AsQueryable()
            join l in libraryRepository.AsQueryable() on c.SomeProperty equals l.SomeOtherProperty
            select new { CustomerName = c.FirstName, LibraryName = l.Name };

There may be limitations to what the query can do, but I did a quick proof of concept on my own codebase between two separate repositories and it worked just fine (with the expected sql firing).

Update

It appears you are attempting two separate queries with a where clause-- I don't believe LINQ2Entities supports what you are attempting to do. Try updating your code to the following:

var customer = (
    from cus in _customer.Query()
    join lib in _library.Query()
    on cus.LId equals lib.Id select cus
).ToList();

Where you are replacing the two 'from/where' queries with a 'join/on'.