How to call Stored Procedure with Joins in Entity framework?

Question

.Net core 3.1

This is my tables

I am not allowed to add any foreign keys or new relation into them. I made a stored procedure that joins other 3 tables to Ordering_Policy table.

create procedure spOrderingPolicy
as
    select op.Id, op.Tyre_Grade, op.Tyre_Group, tb.Tyre_Brand, ss.Status, tc.Cosmetic
    from [dbo].[Ordering_Policy] as op

    left join [dbo].[Tyre_Brand] as tb
    on op.Tyre_Brand_Id = tb.Id
    left join [dbo].[Shearography_Status] as ss
    on op.Shearography_Id = ss.Id
    left join [dbo].[Tyre_Cosmetic] as tc
    on op.Cosmetic_Id = tc.Id
go

The result result is as expected:

So I tried to call the procedure:

public IEnumerable<OrderingPolicy> GetOrder()
{
    using (Ordering_PolicyContext context = new Ordering_PolicyContext())
    {
        var order = context.OrderingPolicies.FromSqlRaw<OrderingPolicy>("spOrderingPolicy").ToList();
        return order;
    }
}

But it ignores anything not in the Ordering_Policy: result

How do I call the procedure and displays all the data like in the query?

Edit: This is the class for OrderingPolicy

namespace Ordering_Policy.Models
{
    public partial class OrderingPolicy
    {
        public int Id { get; set; }
        public string TyreGrade { get; set; }
        public int? TyreGroup { get; set; }
        public int? ShearographyId { get; set; }
        public string CasingLife { get; set; }
        public string Variance { get; set; }
        public string ColorCode { get; set; }
        public string Injury { get; set; }
        public int? MaxCrownPatch { get; set; }
        public int? MaxShoulder { get; set; }
        public int? Age { get; set; }
        public int? CosmeticId { get; set; }
        public int? TyreBrandId { get; set; }
    }
}

What is in your list "order" ? Do you have tha data you need in this variable ? — Koks_rs, Apr 12 '21 at 09:47
Please don't use images where text is possible. Also, we can't see the `OrderingPolicy` class. — Gert Arnold, Apr 12 '21 at 10:41

score 0 · Answer 1 · answered Apr 12 '21 at 16:26

0

You need to create a Keyless Entity Type that matches the resultset returned by your stored procedure, and call OrderingPolicyEx.FromSqlRaw on that DbSet and not OrderingPolicy.

answered Apr 12 '21 at 16:26

David Browne - Microsoft

80,331
6
39
67

score 0 · Answer 2 · answered Apr 13 '21 at 02:47

You should use INNER Join rather than left Join:

This is My demo,with your entity class(a little simplified):

CREATE PROCEDURE [dbo].[spOrderingPolicy]  
as

select op.Id, op.TyreGrade, 
op.TyreGroup,op.CosmeticId,op.ShearographyId,op.TyreBrandId, tb.Brand, ss.status, 
tc.cosmetic
from dbo.OrderingPolicy as op

INNER JOIN [dbo].[Tyre_Brand] as tb
on op.TyreBrandId = tb.Id
INNER JOIN [dbo].[Shearography_Status] as ss
on op.ShearographyId = ss.Id
INNER JOIN [dbo].[Tyre_Cosmetic] as tc
on op.CosmeticId = tc.Id
Go

Result:only matched rows will be showed

score 0 · Accepted Answer · answered Apr 13 '21 at 13:53

I finally able to find the solution: Make new class and context solely to call the columns from the stored procedure

Class:

public partial class Tyre_Detail
{
    public int? Id { get; set; }
    public string Tyre_Grade { get; set; }
    public int? Tyre_Group { get; set; }
    public string Tyre_Brand { get; set; }
    public string Status { get; set; }
    public string Cosmetic { get; set; }
}

The data must have the same name exactly like the columns from the stored procedure. There are no keys in this class so the data type, in this case is int needs to have question mark.

Then create the context file for the class:

modelBuilder.Entity<Tyre_Detail>(entity =>
{
     entity.HasNoKey();

     entity.Property(e => e.Id);
     entity.Property(e => e.Tyre_Grade);
     entity.Property(e => e.Tyre_Group);
     entity.Property(e => e.Tyre_Brand);
     entity.Property(e => e.Status);
     entity.Property(e => e.Cosmetic);
     });

Since this is keyless entities, .HasNoKey() is essential.

Are you sure that we need to mark the int properties as nullable? This does only mean they are mandatory, doesn't it? — dm1988, May 04 '22 at 14:40

How to call Stored Procedure with Joins in Entity framework?

3 Answers3