Using IQueryable with LINQ join tables

Question

I have a method of type IQueryable<T> and here is it's implementation:

 public IQueryable<T> Get()
 {
     return db.Set<T>();
 }

I must write LINQ query where I want to join two tables (left join). It is Identtiy table Users and my custom table PersonalInformation which extend User registration fields and now I want to call this method in my LINQ query and it is good. Here is my Linq:

IRepository<PersonalInformation> personRepository;
IRepository<ApplicationUser> usersRepository;
var query = personRepository.Get();
var test = usersRepository.Get()
                          .GroupJoin(query,
                                     n => n.Id,
                                     m => m.UserId,
                                     (n, ms) => new { n, ms = ms.DefaultIfEmpty() })
                          .SelectMany(z => z.ms.Select(m => new PersonalInfoModel
                          {
                              Name = m.Name,
                              LastName = m.LastName,
                              Email = z.n.Email
                          }));

But I have an error in var test = usersRepository.Get() - System.NotSupportedException. So method Get from personRepository called good but usersRepository method return null. Where I did the mistake?? Thanks

@NetMage yes it is interface IRepository : IDisposable where T : class and there is IQueryable Get(); I have an different context error in linq — M-Misha-M, Sep 18 '17 at 23:05
So it possible that the real type of `usersRepository` does not implement the `Get` operation of `IRepository`. While I don't like it, some classes partially implement interfaces. What is the real type of `personRepository` and `usersRepository`? — NetMage, Sep 18 '17 at 23:09
@NetMage sorry I don't understand it shows (field) IRepository HomeControoller.personRepository. I also create in constructor of HomeContoller next: personRepository = new MainRepository(); and usersRepository = new MainRepository(); — M-Misha-M, Sep 18 '17 at 23:31
@NetMage I think I know where is mistake. In my MainRepository where I implement method get I use return db.Set(); where db is ApplicationDbContext , but it is strange because ApplicationDbContext is Identity context and I don't understand why I get error — M-Misha-M, Sep 18 '17 at 23:46
You can't join two `IQueryables` from two different contexts. I'm sure the *full* exception message tells you something like that. — Gert Arnold, Sep 19 '17 at 09:08
@GertArnold ok , what I should to do with this? How I can rewrite this LiNQ?? — M-Misha-M, Sep 19 '17 at 09:18
Just execute the two queries separately and then compose the `PersonalInfoModel`. — Gert Arnold, Sep 19 '17 at 10:00

score 0 · Answer 1 · answered Sep 19 '17 at 15:28

It looks likely that you are having an error combining queries from two different database contexts. Your custom PersonalInformation is probably in a custom DBContext while Users is in the IdentityDBContext. See this related question. You can either:

Move all of your tables into the same context.

Avoids future confusion between these tables
More efficient if you end up with lots of associations across contexts.
A more involved solution just to get this one example working.

Query your tables separately and combine in memory.

Less scalable if you have a huge number of users.

These operators will cause EF to return the results so you can process in memory.

var people = personRepository.Get().ToList();
var users = usersRepository.Get().ToList();
var infoModels = users.GroupJoin(people,
                                 u => u.Id,
                                 p => p.UserId,
                                 (u, mp) => new { n, ms = ms.DefaultIfEmpty() })
                      .SelectMany(z => z.ms.Select(m => new PersonalInfoModel
                      {
                          Name = m.Name,
                          LastName = m.LastName,
                          Email = z.n.Email
                      }));

Using IQueryable with LINQ join tables

1 Answers1