MVC Core Repository Query Data all at Once

Question

We have Customer transactional table with multiple lookup tables with foreign keys. We want to see the 3 table joined together.

How do I make sure Service call is conducted one? I don't want to grab All unnecessary customer transactions, and then filter in memory. I want to make one service call to find customer transaction > 50 in one sql query.

Repository:

CustomerTransaction GetTransactionbyCustomerId(int customerid)
{
   var result = ct.CustomerTransaction.Where(x => x.CustomerTransactionId == customerid).ToList()
   return result;
}

Service Call:

void GetByCustomerTransactionGreaterthan50(int id)
{
   var newdata = CustomerTransaction.GetByCustomerTransactionId();
   nt.newdata.Where(x => x.PurchaseAmount > 50).ToList()
   return newdata;
}

Models:

public class CustomerTransaction
{
    public int CustomerTransactionId{ get; set; },
    public int ProductTypeId {get; set; }, //joins to ProductTypeTable
    public int StatusID {get; set; },  //joins to StatusTypeTable
    public string DateOfPurchase{ get; set; },
    public int PurchaseAmount { get; set; },
}

public class ProductType
{
    public int ProductTypeId{ get; set; }
    public string ProductName { get; set; },
    public string ProductDescription { get; set; },
}

public class StatusType
{
    public int StatusId{ get; set; }
    public string StatusName{ get; set; },
    public string Description{ get; set; },

}

var result = ct.CustomerTransaction.Where(x => x.CustomerTransactionId == customerid).ToList() - this will materialize your query. You would need to return IQueryable here and invoke toList in service call only once — Bola, May 22 '18 at 08:17
*Don't* try to filter database rows in the "service" method. Do that in the data access method — Panagiotis Kanavos, May 22 '18 at 08:17
@BlueLamp82 you didn't create a generic repository though, you tried to filter data *after* you loaded it. A context *does* offer what a "generic" repository would offer. And ViewModels are meant to serve **views*, not repositories. — Panagiotis Kanavos, May 22 '18 at 08:20
@BlueLamp82 and yes, you can and should have as many DTOs as appropriate for your use cases, not try to shoehorn one big entity object into all roles. If you need to select 5 properties only from multiple entities you *should* create one DTO with only those 5 properties instead of loading the entire object graph. This is the only way to separate different layers and use cases. You can use AutoMapper to map entites to DTOs to ViewModels to ensure eg your views won't break if the entity mapping changes — Panagiotis Kanavos, May 22 '18 at 08:21
@BlueLamp82 don't. That's the job of IQueryable. Put that in the data access method. Return an IQueryable from that method if you want to apply extra filters and delay execution of the query, — Panagiotis Kanavos, May 22 '18 at 08:40
@BlueLamp82 You can read the following q&a thread. Should Repositories return IQueryable? https://softwareengineering.stackexchange.com/questions/192044/should-repositories-return-iqueryable — Razvan Dumitru, May 22 '18 at 12:34
You are correct, repositories should not return iqueryable, then why have a repository, if a repository is pulling all the columns in a table, most queries don't need all the column, so its extra work, unoptimized sql query — , May 22 '18 at 19:36
left question here, https://stackoverflow.com/questions/50475397/mvc-core-repository-query-all-columns — , May 22 '18 at 19:46

Amr Elgarhy · Accepted Answer · 2018-05-22T08:24:52.143

1

You can make a Select projection before the ToList() this will make sure that your sql statement that EF generate include just fields you are projecting too.

Something like:

var result = ct.CustomerTransaction.Where(x => x.CustomerTransactionId == customerid).Select(x=> new CustomerTransaction()).ToList()

and to filter you can include your filter in the where condition:

ct.CustomerTransaction.Where(x => x.CustomerTransactionId == customerid && x.PurchaseAmount > 50)

Or make your GetTransactionbyCustomerId return a Query and filter as you did in the service call

edited May 22 '18 at 08:24

answered May 22 '18 at 08:18

Amr Elgarhy

66,568
69
184
301

The OP actually asks how to read transactions with an amount > 50. The question is badly worded – Panagiotis Kanavos May 22 '18 at 08:19
Whoever downvoted probably didn't read the question at all? It's a *badly worded question*. Did *you* not think the OP was looking for `Include` the first time? – Panagiotis Kanavos May 22 '18 at 08:21
@PanagiotisKanavos thank you, I am confused as well, but I leave my answer, it might help the OP, let's see what he will comment to make it more clear. – Amr Elgarhy May 22 '18 at 08:26

Razvan Dumitru · Answer 2 · 2018-05-22T08:33:31.670

0

Option 1

You will need to adjust your repository in order to be able to add to your query in Service Layer:

Repository:

IQueryable<CustomerTransaction> QueryTransactionbyCustomerId(int customerid)
{
   var result = ct.CustomerTransaction.Where(x => x.CustomerTransactionId == customerid);
   return result;
}

Option 2

Or to create another method in your data access layer:

Repository:

List<CustomerTransaction> GetTransactionByCustomerIdAndAmount(int customerid, int amount)
{
   var result = ct.CustomerTransaction.Where(x => x.CustomerTransactionId == customerid && x.PurchaseAmount > amount).ToList()
   return result;
}

Based on your architecture, you should pick one of these options.

edited May 22 '18 at 08:33

answered May 22 '18 at 08:23

Razvan Dumitru

11,815
5
34
54

It always depends. If you expose an IQueryable, you will have the opportunity to add to that query in another layer, but it's a bad practice to abuse this type of behaviour. – Razvan Dumitru May 22 '18 at 08:27
If you choose Option 2, you'll have to implement many methods and your repository will grow based on your domain. – Razvan Dumitru May 22 '18 at 08:28
1

@BlueLamp82 what you are trying to do *ensures* you'll repeat the code all over the place. Using `IQueryable` means you *won't* have to, you'll only need to apply a `.Where()` to existing queries. Plus, you are trying to apply data access constraints at the wrong level. `GetByCustomerTransactionGreaterthan50` shouldn't be in the business/service layer in the first place. – Panagiotis Kanavos May 22 '18 at 08:32
@BlueLamp82 if you want to apply dynamic filtering criteria and *don't* want to return an IQueryable to the business layer, pass a `Func` a parameter to your DAL with a default of null. If the parameter isn't null, pass it as an argument to a `.Where()` call – Panagiotis Kanavos May 22 '18 at 08:34
1

@BlueLamp82 the *data layer*. Inside `GetTransactionbyCustomerId`. You can chain as many `.Where()` calls as you want in a query. Pass the condition lambda as a `Func` parameter. If it's *not* null, add another `.Where()` before calling `ToList()`, eg `var query=ct.Transactions.Where(x=>CustomerId==a); if (filter!=null) query=query.Where(filter);` – Panagiotis Kanavos May 22 '18 at 08:37
left question here https://stackoverflow.com/questions/50475397/mvc-core-repository-query-all-columns – May 22 '18 at 19:46

MVC Core Repository Query Data all at Once

2 Answers2