1

How to call stored procedures containing multiple joins using Entity Framework Core from ASP.NET Core Web API?

Like suppose there are Department -> Employees -> Address tables. Now if I want to fetch all the departments & related data that is "each department has many employees & each employee has many address ", then we can make an inner join in a stored procedure among these 3 tables. But how to get the grouped data (i.e nested lists) using EF Core in Web API?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dibyo Chatterjee
  • 63
  • 1
  • 8
  • Does this answer your question? [Raw SQL Mapping Stored Procedure Results to POCO/DTO Using Entity Framework Core 2](https://stackoverflow.com/questions/49525660/raw-sql-mapping-stored-procedure-results-to-poco-dto-using-entity-framework-core) – Mick Oct 12 '21 at 03:51
  • 1
    The contents of the stored procedure and the SQL constructs used within it are irrelevant,. You just need to create a class with members matching the return types in the result set produced by SQL Server. – Mick Oct 12 '21 at 03:54
  • The type of the application web api, rich client, console application is also irrelevant. EF works the same way in every type of application. – Mick Oct 12 '21 at 03:56
  • My custom model class contains Department properties, then a List of employees, & in employees model contains a list of Address. The thing is I am able to get data for the department but List of employees is null. When I see the response of the SP, it's returning flat data. But I want each department with a List of Employees & then each employee having a list of Address. – Dibyo Chatterjee Oct 12 '21 at 04:03
  • The result of a SQL query will **always** be "flat" - that's what SQL does - it returns a result set of rows and columns. If you want this hiearchical style result - you'll need to create either XML or JSON inside your stored procedure and return a single XML or JSON string to your caller, which then needs to parse this result and built it back up into a hierarchy of data objects – marc_s Oct 12 '21 at 04:17
  • Yes SQL query will always return flat data. But once I get the response from SP using EF core, how can I then map the result in a hierarchical structure? I tried to use Linq, using groupby deptID, & then running two foreach loops to go through the grouped data & generate the list. But two foreach loops are like taking much time. – Dibyo Chatterjee Oct 12 '21 at 04:21
  • @DibyoChatterjee I suggest edting your question... provide the tables the stored proc and if you have made attempt... post that code. I'd suggest you might need to create several classes... one to get the result set and then you can use linq to map from that structure into the end structure you're after. – Mick Oct 12 '21 at 05:32

1 Answers1

2

But how to get the grouped data (i.e nested lists) using EF Core in Web API?

As far as I'm aware, you can't. The documentation says:

The SQL query can't contain related data. However, in many cases you can compose on top of the query

And it says

Composing with LINQ requires your raw SQL query to be composable since EF Core will treat the supplied SQL as a subquery. ... SQL Server doesn't allow composing over stored procedure calls

All in, you having written a sproc that does SELECT * FROM a. JOIN b ON ... there is then no way for you to say context.A.FromSqlInterpolated("...") and get EF to give you a bunch of A objects with all their related B lists filled out. When you run a normal query like context.A.Include(a => a.B) EF will know what it has requested so it can map the a.* columns to instances of A and the b.* columns to instances of B (simplistically if EF does SELECT a.One, a.Two, b.One, b.Two FROM a JOIN b, it can know the first two columns should be mapped to A instances and the second two to B instances, and it can track A's it has seen before and build a graph of related entities) but just being straight up given a result set and told to map it to a graph of objects isn't going to be universally possible (suppose your sproc does SELECT a.One, a.Two, b.One, b.Two FROM a JOIN b - all EF will see is a result set of "One, Two, One, Two" - how can that certainly be mapped to A:B? And which way round? And what if C has a column One also? ..)

You could consider using Dapper; it will be able to run the sproc, get the table of data and unpack it to A and B (and C etc) per row using its split-on feature and then it's down to you build a tree of objects (dapper calls a method you provide, to build the tree)..

..but then you'll have bought the EF dog and be barking yourself. If you've done this everywhere (used stored procedures full of joins) there seems little point in using EF..

You could perhaps adopt a similar "roll your own" approach where you make an entity that has all the properties that A, B and C do, and then get EF to fill out a collection of it, and then post process it to look for all the repeated A and B and build dictionaries of them to reconstruct the graph; but then you're rebuilding what EF does, which also gets back to the "there's little point using EF"

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • with EF, once the response arrives, If I use groupby deptId, then I have to use foreach loops to loop through the collections in each key & form the nested lists. Is there any better way without using foreach? – Dibyo Chatterjee Oct 12 '21 at 06:40
  • No, there isn't really any "better" way of turning your flat response with repeated A and B back into a tree shaped graph than what you're doing- grouping and forming lists. Any alternative technique will ultimately come back to the same thing – Caius Jard Oct 12 '21 at 06:44
  • Genuinely, the summary of my answer is: "you probably have some unrealistic expectations of what EF can do for you; you'll probably need to either change your way of working so you're using EF as it was designed to be used, or use something else and continue with this manual rebuilding of your data".. – Caius Jard Oct 12 '21 at 06:57
  • So if I eliminate EF, using Dapper..what I saw in multiple mapping is that we have to pass on the model classes to Query<> & also have to specify splitOn. Now, the question is, if my SP is made up of joining between many tables, then I have to pass on all the model classes, right? Is it good way? And for the split on, what if my SP doesn't define the column selection in a proper way, may be in a random way like a.id, a.name, b.id, b.xyz, a.ghhjj, b.rtyjs ?? Will it work?.. If all these are not okay, then suggest me the proper practical way of calling SP's having many joins from .net core api ? – Dibyo Chatterjee Oct 12 '21 at 07:29
  • Er.. You don't have to pass on the model classes, but using EF scaffold to build models that you then query with dapper is a bit.. odd. It'll work, of course - it's just POCOs at the end of the day. As to your SP, if it selects columns in some nice order like `SELECT a.id, a.name, b.id, b.name, c.id, c.name` and therefore makes a good candidate for spliton then great. If you select like `SELECT a.id, b.id, c.id, a.name, b.name, c.name` it's yet another blocker you've thrown in your own way but you can get round it by some manual column mapping techniques in dapper.. I'd ditch the SP, but YMMV – Caius Jard Oct 12 '21 at 08:21