How to retrieve values of custom / user-defined columns through EF?

Question

I require help from anyone who can assist me.

I have a complex query stored in a stored procedure. The objective of this query is to organize the datasets as a tree structure, or hierarchy. Below is a modified version of the actual query.

PROCEDURE [dbo].[tree_structure] @vSet bigint, @vDate varchar(10) AS
IF ISDATE(@vDate) = 1
BEGIN
WITH Hierarchy (Id, Date, SetId, SubsetId, PSubsetId, Order, Lvl) 
AS (
    SELECT Id, Date, SetId, SubsetId, PSubsetId, Order, 0 AS Lvl
    FROM [myTable]
    WHERE Set IN (@vSet)
    AND Date = @vDate
    UNION ALL 
        SELECT t2.Id, t2.Date, t2.SetId, t2.SubsetId, t2.PSubsetId, t2.Order, Lvl + 1
        FROM [myTable] t2 
    INNER JOIN hierarchy h 
        ON t2.PSubsetId = h.SubsetId
        AND t2.Date = h.Date 
        AND t2.SetId = h.SetId
)
SELECT * FROM Hierarchy 
ORDER BY SetId, Order
END

myTable consists of columns Id, Date, SetId, SubsetId, PSubsetId and Order. Column Lvl, however, is a custom / user-defined column and therefore does not exist in myTable.

Executing this in the Microsoft SQL Server Management Studio by EXEC [dbo].[tree_structure] 1, '2021-05-10' will return result as below.

Id	Date	SetId	SubsetId	PSubsetId	Order	Lvl
1	2021-05-10	1	10	NULL	1	0
2	2021-05-10	1	110	10	1	1
3	2021-05-10	1	111	10	2	1
4	2021-05-10	1	1110	111	1	2
5	2021-05-10	1	1115	1110	1	3

Which, in a hierarchy structure, would result as below.

1 (root, level 0)
|- 2 (level 1)
|- 3 (level 1)
   |- 4 (level 2)
      |- 5 (level 3)

I have scaffolded the respective model as below. I added [NotMapped] annotation to Lvl property.

public partial class MyTreeModel
{
    public DateTime Date { get; set; }
    public long SetId { get; set; }
    public long SubsetId { get; set; }
    public long? PSubsetId { get; set; }
    public int? Order { get; set; }
    [NotMapped]
    public int Lvl { get; set; }
}

And my context is as below.

public virtual DbSet<MyTreeModel> Tree { get; set; }

public IQueryable<MyTreeModel> TreeProcedure(long vSet, DateTime vDate)
{
    return Tree.FromSqlRaw("EXEC dbo.tree_structure @vSet, @vDate",
        new SqlParameter("vSet", vSet),
        new SqlParameter("vDate", vDate.ToString("yyyy-MM-dd"))
    );
}

Then, on my API controller, I added

var result = _context.TreeProcedure(setId, date).ToList();

I expected Lvl to return similar result to when executed on Microsoft SQL Server Management Studio, but unfortunately all of them have 0 as its value.

Id	Date	SetId	SubsetId	PSubsetId	Order	Lvl
1	2021-05-10	1	10	NULL	1	0
2	2021-05-10	1	110	10	1	0
3	2021-05-10	1	111	10	2	0
4	2021-05-10	1	1110	111	1	0
5	2021-05-10	1	1115	1110	1	0

Hope someone can help me. I'm fairly new to building .NET Core Application and using the Entity Framework.

Answer 1

This is to provide sample for suggestion in @IvanStoev's answer to replace the SP with TVF.

1. Create the function.

CREATE FUNCTION dbo.TreeStructure(@vSet bigint, @vDate datetime) RETURNS TABLE AS
RETURN
(
    WITH Hierarchy
    AS (
        SELECT Id, Date, SetId, SubsetId, PSubsetId, Order, 0 AS Lvl
        FROM [myTable]
        WHERE Set IN (@vSet)
        AND Date = @vDate
        UNION ALL 
            SELECT t2.Id, t2.Date, t2.SetId, t2.SubsetId, t2.PSubsetId, t2.Order, Lvl + 1
            FROM [myTable] t2 
            INNER JOIN hierarchy h 
            ON t2.PSubsetId = h.SubsetId
            AND t2.Date = h.Date 
            AND t2.SetId = h.SetId
    )
    SELECT * FROM Hierarchy
)

2. Add below configuration within OnModelCreating method.

modelBuilder.HasDbFunction(typeof(myDbContext).GetMethod(nameof(TreeProcedure), new[] { typeof(long), typeof(DateTime) }));

3. Change the SP call method to call TVF.

public IQueryable<MyTreeModelView> TreeProcedure(long vSet, DateTime vDate) => FromExpression(() => TreeProcedure(vSet, vDate));

4. To retrieve the dataset.

var result = await _context.TreeProcedure(1, DateTime.Today).ToListAsync();

Answer 2

Properties marked with [NotMapped] are just ignored by EF Core in all operations, including queries, so even the raw SQL includes such column in the result set, the populated classes by EF Core will just have the default value for the property (in this case, 0 (zero)). The corresponding fluent configuration is even called Ignore.

That's why I'm personally against using [NotMapped] / Ignored members in entity classes - it's so easy to make a mistake to try using them in a LINQ query (for instance, in Where, OrderBy, Select etc.) or like here, and get runtime exceptions or silently skipped data.

So what is the solution. The class you are showing does not really represent a table record, but a view of that table record. Hence it should be a separate class containing the properties returned by the SP.

One way is to create a new class and copy the properties needed. Another (in case you want to reuse all properties of the original class) is to create new class, inherit the original and add the additional properties.

In both cases the new class must be registered in the EF Core model as entity (either keyless or normal) with no associated table.

So let's do the second approach. Remove Lvl property along with the [NotMapped] attribute from MyTreeModel, and add a class similar to this

public class MyTreeModelView : MyTreeModel
{
    public int Lvl { get; set; }
}

and fluent configuration like this

modelBuilder.Entity<MyTreeModelView>(builder =>
{
    builder.HasBaseType((Type)null); // do not consider MyTreeModelView as part of a database inheritance
    builder.ToView(null); // the easiest way to say that it has no associated table
    builder.HasKey(e => e.SubsetId); // or HasNoKey() to treat it as keyless
});

and change the type of the result for SP call

public IQueryable<MyTreeModelView> TreeProcedure(long vSet, DateTime vDate)
{
    return Set<MyTreeModelView>() // <-- this is how you access it w/o explicit `DbSet` property
        .FromSqlRaw("EXEC dbo.tree_structure @vSet, @vDate",
            new SqlParameter("vSet", vSet),
            new SqlParameter("vDate", vDate.ToString("yyyy-MM-dd"))
        );
}

Finally, in case you can replace the SP with TVF, you can utilize EF Core 5.0+ Mapping a queryable function to a table-valued function as shown in the documentation link. But that's only for the TreeProcedure implementation - all other steps (new class, fluent mapping) are still needed.