0

I'm able to use LINQ to Entities to return rows that have, for example, userId 1 with RoleNames = Manager;User;Recorder. 

var tempusersByBranch = (from u in _db.VRT_User
                                 join urb in _db.VRT_UserRoleBranch on u.UserId equals urb.UserId
                                 join r in _db.VRT_Role on urb.RoleId equals r.RoleId
                                 where branches.Contains(urb.BranchId)
                                 group r by new { urb.UserId, urb.BranchId } into rg
                                 select new 
                                 {
                                     UserId = rg.Key.UserId,
                                     BranchId = rg.Key.BranchId,
                                     RoleNames = rg.Select(r => r.RoleName)
                                 });

 var tempusersBybranch2 = (from t in tempusersByBranch.ToList()
                                  select new UserRoleBranchModel
                                  {
                                      UserId = t.UserId,
                                      BranchId = t.BranchId,
                                      RoleNames = String.Join(";", t.RoleNames.ToArray())

                                  });

This is a temp workaround to deal with "LINQ to Entities does not recognize the method 'System.String Join'." usersByBranch does return the correct results.

var usersByBranch = (from t in tempusersBybranch2
                             select new UserRoleBranchModel
                             {
                                 UserId = t.UserId,
                                 BranchId = t.BranchId,
                                 RoleNames = t.RoleNames

                             }).AsQueryable();

When the code below is executed, I get this error message: Unable to create a constant value of type 'xxx.Models.UserRoleBranchModel'. Only primitive types or enumeration types are supported in this context. The issue has to do with join urb in usersByBranch on u.UserId equals urb.UserId. Despite googling, I can't figure out the problem. 

var usersByRole = (from u in _db.VRT_User
                           join urb in usersByBranch on u.UserId equals urb.UserId
                           where urb.RoleName == "User"
                           || urb.RoleName =="Administrator"
                           select new { u.LastName, u.FirstName, u.UserId, u.UserEmail, u.IsDisabled, u.CreatedDate, u.UpdatedDate, u.CreatedById, u.UpdatedById, urb.RoleNames });

Your help is appreciated.

user2142250
  • 349
  • 1
  • 5
  • 18
  • 2
    Basically, LINQ to EF cannot do a join on data in the database and data that is in memory. – Cory Jul 26 '17 at 15:03
  • If you remove the `.ToList()` in the second query, how does that work? – DavidG Jul 26 '17 at 15:05
  • @DavidG That would trigger the error about EF not supporting `string.Join`. – juharr Jul 26 '17 at 15:09
  • If I remove the .ToList() in the second query, I get the error message that "LINQ to Entities does not recognize the method 'System.String Join'." That .ToList() is needed. – user2142250 Jul 26 '17 at 15:09
  • @juharr Ah yes, I should have spotted that. – DavidG Jul 26 '17 at 15:09
  • So you only want users that have only the `User` or `Administrator` roles and no other roles and not both? You could do that filtering on `usersByBranch` select the `UserId` and use that in your next query. – juharr Jul 26 '17 at 15:12
  • Thanks for all your feedback. Since you pointed out that the issue is about joining data in the database and data in memory, I was able to find an example online (http://www.cauldwell.net/patrick/blog/LINQJoiningDataInMemoryWithDataInSQLServer.aspx), and was able to solve the issue. The solution is to get the database data in memory, and then join it with the data in memory. For me, the example I found is easier to understand than the one in this forum. – user2142250 Jul 26 '17 at 17:37

0 Answers0