Avoid multiple calls to database with C# & EF Core

Question

I have a requirement where I need to display a value based on Id. For that, I am making multiple calls to the database, to different tables. Is it possible to reduce the number of calls?

I have switch conditions based on which call to DB occurs. Below is sample

foreach (var items in table1)
{
    foreach (var item in items.List)
    {
         switch (item.condition)
         {
             case condition1:
             case condition2:
                 item.nvalue= string.Join(",", _context.table1.Where(x => ids.Contains(x.Id)).Select(x => x.Title));
                 break;

             case condition3:
                 item.nvalue= string.Join(",", _context.tabl2.Where(x => secondIds.Contains(x.Id)).Select(x => x.newvalu));
                 break;

             case condition4:
                 item.nvalue= string.Join(",", _context.tabl3.Where(x => someIds.Contains(x.Id)).Select(x => x.oldvalue));
                 break;

             case condition5:
                 item.nvalue= string.Join(",", _context.tabl4.Where(x=>textIds.Contains(x.Id)).Select(x => x.note));
                 break;

             default:
                 item.nvalue= "";
                 break;
         }
     }
 }

Thanks in advance

Did you tried this? https://stackoverflow.com/questions/23172888/entity-framework-stored-procedures-multiple-result-sets-with-codefirst This is applicable for EF core also. — dotnetavalanche, Jun 01 '20 at 06:35
It might be possible, but depends. (1) what is the EF Core version? (2) what is the type of `table1` variable here `foreach (var items in table1)` - in-memory collection or db `IQueryable<>`? — Ivan Stoev, Jun 01 '20 at 07:24

Guru Stron · Accepted Answer · 2020-06-01T08:59:58.497

To reduce number of calls you can at least group your items by condition:

var groups = table1
    .SelectMany(t => t.List)
    .GroupBy(i => switch (i.condition)
     {
         case condition1:
         case condition2: 
             return 1;
         case condition3:
             return 2;
         ......
         default: return 0;
     });
foreach(var group in groups)
{
     string nvalue;
     switch (g.Key)
     {
         case 1:
             nvalue = string.Join(",", _context.table1.Where(x => ids.Contains(x.Id)).Select(x => x.Title));
             break;

         case 2:
             nvalue = string.Join(",", _context.tabl2.Where(x => secondIds.Contains(x.Id)).Select(x => x.newvalu));
             break;
         .....
         default:
             nvalue= string.Empty;
             break;
     }
     foreach(vat item in group)
     {
         item.nvalue = nvalue;
     }
}

score 0 · Answer 2 · answered Jun 01 '20 at 06:57

If you want to reduce your database calls, and if your tables are not too large or they are fundamental tables, you can cache the tables in the memory with Caching Data. Full Documentation is over here: Documentation or Working with Cache

I hope it will help you to solve your problem. Good luck.

Avoid multiple calls to database with C# & EF Core

2 Answers2