CODEWITHSUNDEEP
c#asp.net-core.net-coreentity-framework-core
user5992977
user5992977

Reputation:

EF Core logical query

I have C# project which uses EF Core with MVC.

I have a controller representing a "virtual" entity. This entity is created by selecting from two different tables, aggregate each one of them by month and year, and then connecting the two results.

My question is: is it possible (and if so, is it a good solution) to make this sums with EF Core? Or should I just iterate through the elements with C# and aggregate them the way I want?

EDIT: Currently I'm up to this:

 public IEnumerable<MyTable> Get...(int month, int year)
        return _context.MyTable
           //.Include(b => b.)
           .Where(t => t.Month == month && t.Year == year)
           .GroupBy(a => a.BrokerId)
           .Select(param1 => new {
                                   BrokerId = param1.Key,
                                   TotalCommissionAmountBruto = param1.Sum(s => s.CommissionAmountBruto),
                                   TotalCommissionAmountNeto = param1.Sum(s=>s.CommissionAmountNeto)
                       });

This throws an error because the result from the query is not formatted as MyTable object. I've tried creating another viewModel for the result, but with no success(same error about implicit casting)

The viewModel :

public class MyViewModel : DtoBase
{
    public int brokerId { get; set; }
    public int TotalCommissionAmountBruto { get; set; }
    public int TotalCommissionAmountNeto { get; set; }
}

Upvotes: 0

Views: 505

Answers (2)

Kevin LaBranche
Kevin LaBranche

Reputation: 21078

Edit based on code now shown in question:

Your select as shown is returning an anonymous type:

.Select(param1 => new {
                         BrokerId = param1.Key,
                         TotalCommissionAmountBruto = param1.Sum(s => s.CommissionAmountBruto),
                         TotalCommissionAmountNeto = param1.Sum(s=>s.CommissionAmountNeto)
                       });

Return your view model and you should be set:

.Select(param1 => new MyViewModel {
                                    BrokerId = param1.Key,
                                    TotalCommissionAmountBruto = param1.Sum(s => s.CommissionAmountBruto),
                                    TotalCommissionAmountNeto = param1.Sum(s=>s.CommissionAmountNeto)
                       });

Then change your return type on the Action from:

public IEnumerable<MyTable> Get...(int month, int year)

to

public IEnumerable<MyViewModel> Get...(int month, int year)

Then in your view you'll also have to change the model to use the new view model.

Bonus -

You should consider returning a view model instead of directly returning your database model to the view. View Models have many benefits:

Protects against mass assignment vulnerability (the ability to post more than you intended the user to post). See

They can be tailored for each view allowing for things like logic for the view to be implemented that isn't in the database model or is specific to this view only.

They promote loose coupling to allow the view to change for different needs than the database model.

Original answer before questioner added edit to show code:

You can use group by and aggregate functions in EF Core. Take a look at the LINQ GroupBy sample in these docs for a basic example. It can get a lot more complicated than the sample shows.

var query = context.Orders
    .GroupBy(o => new { o.CustomerId, o.EmployeeId })
    .Select(g => new
        {
          g.Key.CustomerId,
          g.Key.EmployeeId,
          Sum = g.Sum(o => o.Amount),
          Min = g.Min(o => o.Amount),
          Max = g.Max(o => o.Amount),
          Avg = g.Average(o => Amount)
        });

Above is the basic idea of grouping by and then using some of the aggregate functions.

If you have the SQL written for it and prefer to use it you can also have EF Core call raw SQL which at first might seem too limiting in that you have to have the result map to an entity (and your case you don't). In 2.1 they added mapping to your own defined model (your virtual entity, View Model) called query types and defining query.

In 2.1 you could create a view model (what you are calling virtual entity), add it to your EF Context and then you can query against it with a database view, another EF query (defining query) and/or anonymous types (with limitations).

For example:

Given a view model:

public class MySpecialModel
{  
  public string Name { get; private set; }
  public int Count { get; private set; }
}

You would add it to your context: 

public DbQuery<MySpecialModel> MySpecialModel {get;set;}

Then you would call it:

var results = context.MySpecialModel.FromSql("select name,count from a join b on b.id=a.id)

If for some reason this doesn't work or is too limiting, I recommend using a micro ORM like dapper that can take your raw SQL and map it to a model.

Your more nuanced question of is it good to?

Short answer - probably but always check your ORMS resultant SQL and adjust accordingly to your needs.

Longer answer - Without knowing the complexity of the query or your existing EF Core logic and the resultant SQL it generates AND your context, how performant does it have to be, how many users, how often will it be called, etc..., we are not able to answer this.

Upvotes: 0

Vivek Nuna
Vivek Nuna

Reputation: 1

There are many ways to achieve this requirement. If there is a parent-child relationship between the tables you can use Include and ThenInclude.

You can run the stored procedure also using FromSql method and select the required data.

You can also use projection query using Select extension method.

You can refer to the official documentation provided by Microsoft for all of the above.

Upvotes: 1

Related Questions