CODEWITHSUNDEEP
c#linq
vinay K
vinay K

Reputation: 13

I'm getting an error when trying to join against multiple tables in a query

I getting this error when I join:

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: The specified LINQ expression contains references to queries that are associated with different contexts.

 var rightsList = RoleRightService.GetRoleRights<RoleRight>().Where(x => x.RoleCode == role && x.CompanyId == USER_OBJECT.CompanyId).AsEnumerable();

 var securables = SecurableServices.GetSecurable<Securable>()
                        .GroupBy(a => new { a.RegistrationType_LookUpId })
                        .Select(r => new
                        {
                            id = r.Select(x => x.SecurableID),
                            registrationType = r.Key.RegistrationType_LookUpId,
                            RegistrationTypeName = r.Select(x => x.RegistrationType.LookUpDescription).Distinct().FirstOrDefault(),
                            IsChecked = false,
                            pageList = r.GroupBy(b => new { b.PageID })
                                .Select(p => new SecurableViewModel
                                {
                                    Id = p.Where(x => x.PageID == p.Key.PageID && x.Type == 1).Select(x => x.SecurableID).FirstOrDefault(),
                                    PageId = p.Where(x => x.PageID == p.Key.PageID && x.Type == 1).Select(x => x.PageID).FirstOrDefault(),
                                    PageName = p.Where(x => x.PageID == p.Key.PageID && x.Type == 1).Select(x => x.PageDescription).FirstOrDefault(),// && rr.AccessRight !=0
                                    IsChecked = rightsList.Where(rr => rr.SecurableID == (p.Where(x => x.PageID == p.Key.PageID && x.Type == 1).Select(x => x.SecurableID).FirstOrDefault())).Count() > 0,
                                    operationList = r.Where(x => x.PageID == p.Key.PageID && x.Type == 2)
                                    .Select(o => new RoleRightViewModel
                                    {
                                        Id = o.SecurableID,
                                        OperationID = o.OperationID,
                                        OperationName = o.OperationDescription,
                                        IsChecked = rightsList.Where(rr => rr.SecurableID == o.SecurableID).Count() > 0,
                                    })
                                    .ToList()
                                }).ToList()
                        }).ToList();

I am getting error

The specified LINQ expression contains references to queries that are associated with different contexts.

For this line:

IsChecked = rightsList.Where(rr => rr.SecurableID == (p.Where(x => x.PageID == p.Key.PageID && x.Type == 1).Select(x => x.SecurableID).FirstOrDefault())).Count() > 0,

is there possibilty to right delegate for this

Upvotes: 1

Views: 66

Answers (1)

gareththegeek
gareththegeek

Reputation: 2418

It looks like you are using multiple EF entity contexts, possibly to query more than one database. EF is not able to perform a linq to entities query across more than one EF context.

In order to execute this query without error is will be necessary to use linq to objects instead by projecting the data from each context into memory before combining them. Please note this may have a negative performance impact since all objects will need to be fetched into memory before being filtered down.

Try adding a .ToList() between your GroupBy and Select statements:

var securables = SecurableServices.GetSecurable<Securable>()
    .GroupBy(a => new { a.RegistrationType_LookUpId })
    .ToList()                
    .Select(r => new
    ...

Upvotes: 0

Related Questions