CODEWITHSUNDEEP
c#linqdbcontext
K.Z
K.Z

Reputation: 5075

Combine LINQ result from multiple DbContext into single Object

I have 2 LINQ, each with different DbContext. I want to add the result of both into single object reference. that is peopleEntity and WebSyncDetailObject (reference to linq select new{..})

Class Object

public class WebSyncDetailEntity
{
    public Web_SyncMatchesEntity WebSyncMatch { get; set; }

    public Web_LookupsEntity WebLookups { get; set; }

    public List<PeopleEntity> People { get; set; }
}

LINQ Processing class

 private List<WebSyncDetailEntity> ProcessGetWebSyncDetail()
    {
        List<WebSyncDetailEntity> WebSyncDetailObject = null;

        using (var _uow = new UCAS_WebSync_AdminTool_UOF())
        {
            try
            {
                List<PeopleEntity> peopleEntity = null ;

                int? personCode = _uow.Web_SyncMatchesRepository.GetAll().Where(x => x.SUBMISSION_ID == "28105").Select(y => y.PERSON_CODE).FirstOrDefault() ;

                using(var _fes_uow = new FES_UOF())
                {
                    peopleEntity = _fes_uow.PeopleRepository.GetAll().Where(x => x.PERSON_CODE == personCode).ToList();
                    var t = "d";
                }

               WebSyncDetailObject = (from esm in _uow.Web_SyncMatchesRepository.GetAll()
                                      join lup in (_uow.Web_LookupsRepository.GetAll().Where(a => a.RV_DOMAIN == "match_method")) on esm.MATCH_METHOD equals lup.LOOKUP_KEY
                                      where esm.SUBMISSION_ID == "28105"
                                      select new WebSyncDetailEntity {WebSyncMatch = esm, WebLookups = lup, People = peopleEntity }).ToList();

                //throw error here!
            }
            catch(Exception exp)
            {
                Debug.WriteLine(exp.ToString());
            }
        }

            return null;
    }

enter image description here

I want final result in WebSyncDetailEntity object

2nd error

enter image description here

Upvotes: 0

Views: 718

Answers (3)

K.Z
K.Z

Reputation: 5075

I have found the answer; loop through list and add object to existing list object where matches condition 

 public class WebSyncDetailEntity
{
    public Web_SyncMatchesEntity WebSyncMatch { get; set; }

    public Web_LookupsEntity WebLookups { get; set; }

    public PeopleEntity People { get; set; }
}

...

 private List<WebSyncDetailEntity> ProcessGetWebSyncDetail()
    {
        List<WebSyncDetailEntity> WebSyncDetailObject = null;

        using (var _uow = new UCAS_WebSync_AdminTool_UOF())
        {
            try
            {
                List<PeopleEntity> peopleEntity = null ;

                int? personCode = _uow.Web_SyncMatchesRepository.GetAll().Where(x => x.SUBMISSION_ID == "28105").Select(y => y.PERSON_CODE).FirstOrDefault() ;

                using(var _fes_uow = new FES_UOF())
                {
                    peopleEntity = _fes_uow.PeopleRepository.GetAll().Where(x => x.PERSON_CODE == personCode).ToList();
                }

                WebSyncDetailObject = (from esm in _uow.Web_SyncMatchesRepository.GetAll()
                                         join lup in (_uow.Web_LookupsRepository.GetAll().Where(a => a.RV_DOMAIN == "match_method")) on esm.MATCH_METHOD equals lup.LOOKUP_KEY
                                         where esm.SUBMISSION_ID == "28105"
                                         select new WebSyncDetailEntity { WebSyncMatch = esm, WebLookups = lup }).ToList();

                  int index = 0;
                  foreach(var item in WebSyncDetailObject)
                  {
                      WebSyncDetailObject[index].People = peopleEntity.Where(x => x.PERSON_CODE == WebSyncDetailObject[index].WebSyncMatch.PERSON_CODE).FirstOrDefault();
                      index++;
                  }
            }
            catch(Exception exp)
            {
                Debug.WriteLine(exp.ToString());
            }
        }
            return null;
    }

Upvotes: 0

Kosala W
Kosala W

Reputation: 2143

I think you can do something like this.

var resultsFromDb = (from esm in _uow.Web_SyncMatchesRepository.GetAll()
                                      join lup in (_uow.Web_LookupsRepository.GetAll().Where(a => a.RV_DOMAIN == "match_method")) on esm.MATCH_METHOD equals lup.LOOKUP_KEY
                                      where esm.SUBMISSION_ID == "28105" select new {esm = esm, lup = lup, peopleEntity = peopleEntity}).ToList(); // Here we break the linq to sql query

// Then run a select query on the above collection to get what we want.  
WebSyncDetailObject  = resultsFromDb.Select(r => new WebSyncDetailEntity {WebSyncMatch = r.esm, WebLookups = r.lup, People = r.peopleEntity }).ToList();

Upvotes: 0

Andy Briggs
Andy Briggs

Reputation: 485

I think the reason you're getting an exception here is that it's trying to include peopleEntity in the query being sent to the database. This won't work, as complex datatypes aren't supported in queries.

A simple fix would be to leave the People field null when you first create the WebSyncDetailObject in the query, then set it to peopleEntity on the next line.

Upvotes: 1

Related Questions