How to write LINQ query that selects a property which is a string concat of a collection of a nested entity properties?

Question

Trying to be more efficient with my queries, but not sure how to write this all as one query. I've got a domain model:

public class UserReport : Entity
{
    public string Name { get; set; }
    public List<string> Statuses { get; set; }
    public List<GroupModel> Groups { get; set; }
    public string Email { get; set; }
    public string OfficeStates {get; set;} //Comma delimited list
}

public class GroupModel : Entity
{
    public string Name {get; set;}
    public string Type {get; set;
}

Which is a "compound entity", if you will. The standard entities representing those collections are M2M relational entities with the User object:

public class User : Entity
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public ICollection<Status> Statuses { get; set; }
    public ICollection<Group> Groups { get; set; }
    public ICollection<Office> Offices { get; set; }
}

public class Office : Entity
{
    //other properties
    public State State { get; set; }
}

public class State : Entity
{
    public string Name { get; set; }
    public string Abbreviation { get; set; }
}

So far I've got:

Context.DbSet<User>.Select(user => new UserReport()
    {
       Name = user.FirstName + ", " + user.LastName,
       Email = user.Email,
       Statuses = user.Statuses.Select(status => status.Name).ToList(),
       Groups = user.Groups.Select(group => new GroupModel(){ Name = group.Name, Type = group.Type.Name}).ToList(),
       OfficeStates = string.Join(",", user.Offices.Select(office => office.State.Abbreviation).ToList())
    }).ToList();

Which throws an error:

LINQ to Entities does not recognize the method 'System.String Join(System.String, System.Collections.Generic.IEnumerable`1[System.String])' method, and this method cannot be translated into a store expression.

I totally get what this is saying and I know if I called .ToList() before my select it would work, but I need to have this be one query, so I can still have an IQueryable to apply filtering later. Surely there must be some way in LINQ to do this. It can be done in SQL: Concatenate many rows into a single text string?, how can it be done via LINQ?

It's not possible with LINQ to Entities. You need to change your domain model. — Ivan Stoev, Oct 04 '16 at 17:43
That just seems insane to me, I see questions 8 years old about this and it's a pretty common request, how can there be no LINQ to SQL translation after all this time? Is it possible to call a sproc or function as a subquery if I made something to do in SQL sproc? — SventoryMang, Oct 04 '16 at 17:49
Well, that's what it is :) I don't see what's the problem to return the data in a structured way (e.g. `List`) and concatenate it at the place where you need that. It's similar to returning typed numbers and dates, and format them to strings where needed. — Ivan Stoev, Oct 04 '16 at 17:56
It makes filtering pretty difficult and tedious when passing the filtering options from a grid of the page onto your query. A grid has no concept of collections. I've written a generic extension method that takes some grid filtering parameters and applies them to an IQueryable of my UserReport. This is super helpful, however if I can't select my entire report model in one LINQ query, I have to manually write translations for each report Entity for filtering, or select all records and then filter, the former is much more development time to implement, and the latter is horribly performant — SventoryMang, Oct 04 '16 at 18:05
Still can't get the case, but anyway. But you are already inconsistent - `List Statuses` vs `string OfficeStates`. Is this because you need to apply filtering on `OfficeStates` and not on `Statuses`? See, you could probably create db views and query them, but then what's the point of using EF? Neither string conversions nor string formatting is supported by EF (very basic `ToString` and simple concatenation with `+` - that's all). — Ivan Stoev, Oct 04 '16 at 18:46
For the case. I just have method that works like this GetBaseReportQuery().ApplyFilters(List filters, Sort sort). Both work off IQueryable, but UserReport is just a domain model, not an EF entity. To Consistency: you are correct, in reality, all my Report properties are flat and all are concat strings because I need filtering on all of them. No views, you see the issue with that=). I guess what I will do is use dynamic LINQ (my ApplyFilters uses it already) and see if I can write my logic to transform list of string into a dynamic linq string query for UserReport. — SventoryMang, Oct 04 '16 at 19:21
Sounds like a good plan :) You don't even need Dynamic LINQ, building dynamic filter expressions is not so hard. The only benefit of Dynamic LINQ is dynamic projection or dynamic grouping etc. — Ivan Stoev, Oct 04 '16 at 19:28
Do you have an example of that? Like from our classes above, someone with a grid on page filters OfficeStates to where it contains NY. The UserReport lamba would be .Where(x => x.OfficeStates.Contains("NY"), but the User lamba would be .Where(x => x.Offices.Any(y => y.State.Abbreviation == "NY"), how to bridge that gap without writing full business logic for every single property of every single report domain model to convert it to the corresponding entity lambda? — SventoryMang, Oct 04 '16 at 21:08

score 0 · Answer 1 · answered Oct 04 '16 at 17:44

You can see that even in pure SQL that is not trivial (for xml, declaring variables etc). As far as I know there is no way to do exactly what you want with pure LINQ. However, at least in some cases you can do that using one query, though this query will not be the same as you would do this yourself in pure SQL. In your case that would be something like this:

Context.DbSet<User>.Select(user => new // note, anonymous type here
{
   Name = user.FirstName + ", " + user.LastName,
   Email = user.Email,
   Statuses = user.Statuses.Select(status => status.Name), // no ToList - this won't work
   Groups = user.Groups.Select(group => new GroupModel(){ Name = group.Name, Type = group.Type.Name}), // no ToList()
   OfficeStates = user.Offices.Select(office => office.State.Abbreviation) // no ToList(), no String.Join()
}).ToList() // here we materizized, and now we can concatenate strings by hand
.Select(c => new UserReport {
     Name = c.Name,
     Email = c.Email,
     Statuses = c.Statuses.ToList(),
     Groups = c.Groups.ToList(),
     OfficeStates = String.Join(",", c.Offices)
});

In simple cases I tested on, this generates one query to database, and this query receives only columns you need (though as I said - generated query is not the same you would use in SQL, conceptually). So I suggest to try this approach and see what query is generated (note also my comments in code above).

This wouldn't fulfill my requirement, I mentioned I know I can call .ToList() but then I can't apply filtering after the fact. Or well I could, but then I would have to always query all entities first before filtering which would be terribly inefficient and performant heavy when I am querying 50k rows. — SventoryMang, Oct 04 '16 at 17:51
But note you receive all data you need before calling ToList. You dont need to apply any filtering after (except of course you want to filter on that aggregated string itself). You can apply all other filtering before calling ToList — Evk, Oct 04 '16 at 17:55
I do need to filter on the aggregated strings because that's how the data is presented to the user in a grid right? Like jquery datables or something, they see a column, click filter where X column is equal to Y. Now I need to filter off that, and want to do so in the query itself so I don't have to 1) write my own custom filtering per entity and 2) don't load all entities first and then filter down to like 20 of 5000 due to filtering, that's inefficient. — SventoryMang, Oct 04 '16 at 21:01
Well I see your point. I usually just use sql views (sometimes materialized views) in similar situations, maybe you could also do that (though I usually use them when select queries are more complex than that). All other options you already know I suppose. — Evk, Oct 04 '16 at 21:08

How to write LINQ query that selects a property which is a string concat of a collection of a nested entity properties?

1 Answers1