how to get multiple resultsets using EF/Linq

Question

what is the best way to get multiple resultsets to my api using linq or EF

as i have around 25 diffrent resultset

but i only get out the first resultset to swagger using

       [HttpGet]
       [Route("tire-tabel")]
       public List<DeviationCalculation_Result> TireTabel(decimal presentWidth, decimal presentAspectRatio, string presentRimSize, int maxDeviation)
                   {
                       using (var context = new OminiTireEntities())
                       {
                           String sql =
                           "SET NOCOUNT ON; " +
                           "EXEC [Tabel].[DeviationCalculation] " +
                           "@PresentWidth= '" + presentWidth + "', " +
                           "@PresentAspectRatio= '" + presentAspectRatio + "', " +
                           "@PresentInches= '" + presentRimSize + "', " +
                           "@MaxDeviation= '" + maxDeviation + "' ";


                           List<SqlParameter> sqlParams = new List<SqlParameter>();
                           sqlParams.Add(new SqlParameter("PresentWidth", (object) presentWidth ?? DBNull.Value));
                           sqlParams.Add(new SqlParameter("PresentAspectRatio", (object)presentAspectRatio ?? DBNull.Value));
                           sqlParams.Add(new SqlParameter("PresentInches", (object)presentRimSize ?? DBNull.Value));
                           sqlParams.Add(new SqlParameter("MaxDeviation", (object)maxDeviation ?? DBNull.Value));
                           var result = context.Database.SqlQuery<DeviationCalculation_Result>(sql).ToList<DeviationCalculation_Result>();               

return result;
                       }
                   }

https://www.codeproject.com/Tips/1072284/Return-Multiple-Result-Set-using-Entity-Framework - google before you ask! you're lucky I can't vote down. — , Sep 14 '17 at 08:03

score 0 · Answer 1 · edited Nov 10 '17 at 20:03

0

In short Entity Framework doesn't support this use case natively:

in case of classic EF 5 or 6 take a look to this article (you'll need to use low-level ADO.NET data reader)
EF Core (event version 2) doesn't support multiple resultsets (see issue on github)

If you want to read multiple results into EF models you can use NReco.Data library in addition to EF: How to get multiple RecordSets from StoredProcedure with NReco.Data in Asp.NetCore

edited Nov 10 '17 at 20:03

marc_s

732,580
175
1,330
1,459

answered Sep 13 '17 at 13:12

Vitaliy Fedorchenko

8,447
3
37
34

@SPDev I've no idea what do you mean. – Vitaliy Fedorchenko Sep 14 '17 at 07:53
your answer is wrong and mine which you voted down is the right one. https://www.codeproject.com/Tips/1072284/Return-Multiple-Result-Set-using-Entity-Framework – Sep 14 '17 at 08:01
2

@SPDev first, I didn't vote down anything. Second, my answer is correct - take a look to the link for EF 5/6 in my answer, it describes the same approach: ADO.NET should be used directly, and data reader results could be mapped to models with Translate method. In overall, EF cannot read multiple result sets natively, without need to compose SqlCommand and execute data reader manually. – Vitaliy Fedorchenko Sep 14 '17 at 08:28

score 0 · Answer 2 · answered Sep 13 '17 at 13:58

0

In a simple case you can create SQL function which returns the results as combined from 2 or more tables. Then update the .edmx and execute the function from EF.

answered Sep 13 '17 at 13:58

narekye

114
3

yeah creating the whole funktion from the storedprocedure and then just put all thoose result in a list that might be the simplest way – omini data Sep 14 '17 at 06:12

how to get multiple resultsets using EF/Linq

2 Answers2