How to Merge two Entity framework queries into on in MCV

Question

I am new in my company, and they hired me to make a MCV version of their WPF application. We use entity framework to get data. Here's the dilemma: I have a lot of tables to fetch from, simultaneously into one query. I do this by:

 ... context.Citizens.Expand("person,person/passport") ...

The expand method works but has a limit on about 15 tables added. Sadly I reached the limit and need more. So the way the WPF application gets around this, is to take the previous queries into a DataServiceCollection and use the .Load<T> method on it. The way I understand it, it takes whatever EF data model and adds fields that are missing and fills in the data everywhere. (kind of like a super merge).

MVC does not support this magical function, so I need help to make something that does the exact same thing.

Example: the tables before the .Load method:

 Citizen { id, name, personId }
    Person { id, LoginInfo, PassportId }
    Passport { id, RealName, passportNumber etc etc }

The DataServiceCollection after the .Load method:

 Citizen {id, name, personId, LoginInfo, PassportId, RealName, passportNumber etc etc}

Does anyone know how I could achieve this? I am hoping for an extension for List<T> so it can do it. But Anything would help.

Btw: I dont need the content update of the DataServiceCollection<T> just need the data merge function.

EDIT:

There seemed to be some confusion on the word "merge".. so let me explain:

The WPF application does something like this:

var expandString =
            "Person"
            + ",Person/foobar"
            + ",Person/foobar/bar"
            + ",Person/foobar/foofoofoobar"
            + ",Person/foobar/foofoofoobar/Type"
            + ",Foo1"
            + ",Foo1/foofoo"
            + ",Foo2"
            + ",Foo2/foofoo"
            + ",Foo3"
            + ",Foo3/foofoo"
            + ",Foo4"
            + ",Foo4/foofoo"
            + ",Foo5"
            + ",Foo5/foofoobar"
            + ",Foo5/foofoobar/bar";

IQueryable<Citizen> citizensQuery = 
    from cit in context.Citizens.Expand(expandString)
    where cit.Person.boobar.bar.barfoo.fuuId == _fuuId
    select cit;

var result = new DataServiceCollection<Citizen>(citizensQuery, 
    TrackingMode.None);

and then when you want more extra tables added to "result", you just use the .Load method:

var moreExpandStrings = 
      "Citizenfoo"
    + ",foo6"
    + ",foo7"
    + ",foo8"
    + ",foo9"
    + ",foo10"
    + ",foo11";

result.Load(
    context.Citizens.Expand(moreExpandStrings)
    .Where(x => x.Person.boobar.bar.barfoo.fuuId == _fuuId
));

This result.Load<T>() method takes the T and adds the extra variables onto the EF model and fills the data from database in. THIS is what Im looking for in a List extension method. Can be code heavy if need be.

I found a solution that works around the problem.. I just create a new instance of the ContextContainer (ctx) for each call that uses the same Model. In my case the Citizen was already created and filled with data once, and any subsequent attempt to fill data into it, would instead return the other query.. When i use a different ctx instance for each time i use Citizen i get all the data i want, and then c# makes it easy to use LINQ to iterate those two lists for data whenever i need something from the other query. Its not clean, but its what can be done.. — user15622687, May 07 '21 at 12:23

score 0 · Answer 1 · answered May 04 '21 at 10:31

0

If you are just looking for the data merge function you can use AddRange() method by applying the Linq. here's a solution posted before

answered May 04 '21 at 10:31

Taylan Yuksel

345
3
12

score 0 · Answer 2 · answered May 04 '21 at 14:51

That looks more like a JOIN than a MERGE. The EF equivalent of Expand is Include. If you are just looking to build the projection from your question and your entities and navigation properties are properly configured you can do a simple LINQ projection without the INCLUDEs (if relationships are not configured you can use JOIN). Citizen would have Person navigation property and Person would have a Passport nav:

var CitizenInfoList = context.Citizens
   .Where(x => x.Foo == "Bar")
   .Select(x => new myModel {
       id = x.Id,
       name = x.Name,
       LoginInfo = x.Person.LoginInfo,
       PassportId = x.Person.PassportId,
       RealName = x.Person.Passport.RealName ,
       PassportNumber = x.Person.Passport.PassportNumber 
   })
   .ToList();

To just dump the entities:

var CitizenInfoList = context.Citizens
   .Include(p => p.Person.Select(c => c.Passport))
   .ToList();

Another option is a database View or Stored Procedure.

After reading more into the documentation of web + EF, it doesnt sound bad what you suggest here.. i will try it out and see if i can make sence of it.. — user15622687, May 06 '21 at 07:47
Sadly it didnt pan out.. Once I have Queried the Citizens model, any subsequent queries will just return that result ignoring the other query request. the .Include clause doesnt exist on DataServiceQuery objects. — user15622687, May 06 '21 at 13:04

How to Merge two Entity framework queries into on in MCV

2 Answers2