2

I wonder if there is a better solution to my problem then this one https://entityframeworkcore.com/knowledge-base/44877455/get-all-columns-except-one-in-efcore

I have several models with many columns (lets say 20) and few columns are heavy - blobs. Naturally, I would like to have a method retrieving the data (list of them) in a light way - i.e. excluding these few columns. So far the only method working is the one proposed in the link above, i.e.

original model:

{
    public Rating() { }
    public int IdRating { get; private set; }
    public string IdUser { get; set; }
    public decimal Value { get; private set; }
    public string Comment { get; private set; }
    public bool IsEnabled { get; set; }
    public int IdCorrespondent { get; private set; }}

then the lighter version 

public class RatingView
{
    public Rating() { }
    public int IdRating { get; private set; }
    public decimal Value { get; private set; }
}

and the data retrieval is

public List<RatingView> ListRatings()
    {
        return _context.Ratings.Select(x => new RatingView
        {
            IdRating = x.IdRating ,
            Value = x.Value ,
        }).ToList();

    }

the first problem is that there are too many columns to set this way and secondly there are several similar classes, so the solution is not really nice and error prone for future changes of the models. Automapper looks like a nice approach but the queries generated are not optimal - all columns are retrieved.

Thanks, arbus

arbus
  • 121
  • 7
  • 1
    It depends. But I would consider to move less accessed columns to separate table and use `join` when they are really necessary. Say, if extra (heavy) columns are used 1/100 times of light queries -- this can be a good approach. Otherwise, you have to write manual projections using `select`. – Crypt32 Mar 11 '20 at 16:21
  • 1
    AM is designed to have different DTO-s for different cases. That being said, http://docs.automapper.org/en/latest/Queryable-Extensions.html#explicit-expansion. – Lucian Bargaoanu Mar 11 '20 at 17:14
  • thanks Crypt32 , splitting the table on the level of the Database is the best approach but unfortnately it is available only on the Database design stage (inhereted in my case). The second sugestion I didint fuly understand. Are you suggesting something like that var All=_context.Ratings.Where(...); var query=All.Select(x => new RatingView { IdRating = x.IdRating , Value = x.Value , }) So in the end I dont see how it is different from the initial solution? Regards, arbus – arbus Mar 12 '20 at 09:34
  • thanks Lucian, interestingly enough but using .ProjectTo<> on DbSet seems to result in the optimal query!! I will make a few more tests, but it looks really promising! – arbus Mar 12 '20 at 09:48

1 Answers1

3

Looking in other stackoverflow posts and taking the help of several people I found 5 solutions for the problem of which the first one is the best for me - credit to Lucian Bargaoanu

1) based on the Automapper

http://docs.automapper.org/en/latest/Queryable-Extensions.html#explicit-expansion
In fact, there is no even need in creating the separare light class with a limited number of fields - the Automapper can ignore heavy fields and the generated SQL query will not be using them.

var ratings = _db.Ratings.Where(...).ProjectTo<Rating>(new MapperConfiguration
    (cfg => { cfg.CreateMap<Rating, Rating>()
        .ForMember(d => d.Comment, m => m.Ignore())
        .ForMember(d1 => d1.IdUser, m1 => m1.Ignore()); })).ToList();

If you want to use the Light class like RatingView - just map to it without ignoring anything.

2) Database design When designing your Table, dont include these heavy columns to the table and instead - create a view if you need all in one model. Unfortunately, if you are inheriting the table it is not an option

3) EF Core Table splitting (proposed by Crypt32) It is possible to perform table splitting using EF Core mechanism

https://learn.microsoft.com/en-us/ef/core/modeling/table-splitting it is as well explained here EFCore Map 2 entities to same table

I think it is possible from EF core version 3, which is on the time of writing is not available for me as I am using EF core with Oracle. There is no need for views in the Database, there is no need to map and list all properties, but still there is a need to create these light model classes.

4) The initial solution which works as well. https://entityframeworkcore.com/knowledge-base/44877455/get-all-columns-except-one-in-efcore
As mentioned before, the disadvantage is the need to create many light classes and to writr the cumbersome code with many properties and then to support all these entities through the evolution of your solution.

Kevin Dockx advised two other possibilities, which are both very good.

5) View creation and mapping the light model class to it as suggsted here:

https://gavilan.blog/2018/07/29/entity-framework-core-2-1-query-types-working-with-views/

The disadvatnage is the need to support light classes and the views - kind of the same issue as with the solution 3.

6) SQL queries it is possible to use the mechanism of sql queries, something like

var ratingLIghts= dbContext.Database.SqlQuery<Rating>("select ... from Ratings");

7) Linq extensions there are suggestions to extend Linq with queries like SelectExcept as in here

How to Except Property instead of Select in Lambda LINQ

the one piece of code suggeste dthere doesnt work optimally as it just populates by NULL the columns to be excluded but the query execute don the database is reading all columns - not acceptable but I believe it is posisble to implement it differently

So, from my point of view the most elegant solution with minimum amount of code and less prone to futher refactoring potenial problems is the first one - thank you Lucian Bargaoanu!

arbus
  • 121
  • 7