CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworklinqlambda
Nadorkhan
Nadorkhan

Reputation: 21

Linq Lambda Error : The specified LINQ expression contains references to queries that are associated with different contexts

I receive this error, and I tried a lot to solve it, but I receive other errors, is there a solution?

using (var contextDb1 = new Db1Context(System.Web.HttpContext.Current.Session["DB1ConnectionString"].ToString(), false))
{
    using (var contextDb2 = new Db2Context(System.Web.HttpContext.Current.Session["DB2ConnectionString"].ToString(), false))
    {   
        var messagesList = contextDb2.Messages
            .Select(m => new MessagesViewModel
            {
                UserName = contextDb1.UsersInfo.FirstOrDefault(u=>u.Id == m.UserId).UserName,
                MessageId = m.MessageId,
                MessageText = m.MessageText,
                DateTime = m.DateTime
            })
            .ToList();

        return messagesList;   
    }
}

Upvotes: 1

Views: 161

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

You cannot query two different databases via the same LINQ Query. But you can use intermediate result to execute two queries to databases and then combine result.

using (var contextDb1 = new Db1Context(System.Web.HttpContext.Current.Session["DB1ConnectionString"].ToString(), false))
using (var contextDb2 = new Db2Context(System.Web.HttpContext.Current.Session["DB2ConnectionString"].ToString(), false))
{   
    var rawMessages = contextDb2.Messages
        .Select(m => new 
        {
            m.UserId
            m.MessageId,
            m.MessageText,
            m.DateTime
        })
        .ToList();

    var userIds = rawMessages.Select(x => xu.UserId);
    
    var usersInfo = contextDb1.UsersInfo.Where(u => userIds.Contains(u.Id))
        .Select(u =>  new 
        {
            UserId = u.Id,
            UserName = u.UserName
        });

    var messageQuery = 
        from m in rawMessages
        join u in usersInfo.AsEnumerable() on m.UserId equals u.UserId into gj
        from u in gj.DefaultIfEmpty()
        select new MessagesViewModel
        {
            UserName = u?.UserName,
            MessageId = m.MessageId,
            MessageText = m.MessageText,
            DateTime = m.DateTime
        };
    
    var messagesList = messageQuery.ToList();

    return messagesList;   
}

Upvotes: 1

Related Questions