使用FromSql和include时出错（“调用存储过程时不支持Include操作。”） [英] Error when using FromSql and include ("The Include operation is not supported when calling a stored procedure.")

问题描述

我使用Asp.Net Core版本2和代码优先。我尝试使用 .FromSql 调用存储过程。我做的跟微软所说的一样：

I used Asp.Net Core version 2 and code-first. I tried to use .FromSql to call a stored procedure. I did the same as Microsoft said:

var blogs = context.Blogs
                   .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
                   .Include(b => b.Posts)
                   .ToList();

而我的存储过程仅包含代码行

and my stored procedure only contains the line of code

 ALTER PROCEDURE [dbo].[GetCouffierSearch]  
 AS
 BEGIN
     SET NOCOUNT ON;

     SELECT * 
     FROM AspNetUsers
END

我在API中的代码：

public IQueryable<ApplicationUser> SelectNearByUser()
{
    var query = "execute dbo.GetCouffierSearch";
    var res = _context.Users.FromSql(query);
    return res.Include(x => x.CoiffeurServiceLevels);
}

和我的课程 ApplicationUser 包含类型为 ICollection 的 CoiffeurServiceLevels 的定义：

and my class ApplicationUser contains definition for CoiffeurServiceLevels with type of ICollection:

public class ApplicationUser: IdentityUser
{
        public ApplicationUser()
        {         
            //CoiffeurServiceLevels = new Collection<CoiffeurServiceLevel>();
            //Requests = new Collection<Request>();
            //Tickets = new Collection<Ticket>();        
            //UserRatings = new Collection<Rating>();
            //CouffierRatings = new Collection<Rating>();
        }

        public string PhotoUrl { get; set; }

        public string Lat { get; set; }
        public Language Language { get; set; }

        public string Address { get; set; }
        public string FullName { get; set; }
        public string Lng { get; set; }
        public string PersonalId { get; set; }

        public Place Places { get; set; }
        public long? PlaceId { get; set; }

        public int? CouffierNumber { get; set; } = 1;

        public long CityId { get; set; }
        public City City { get; set; }

        public bool IsSuspended { get; set; }
        public bool IsOnline { get; set; }
        public string ConfirmedToken { get; set; }
        public string ResetPasswordlToken { get; set; }
        public  DateTime BirthDate { get; set; }

        public bool Gender { get; set; } 

        //#region RelationsClass

        public virtual ICollection<CoiffeurServiceLevel> CoiffeurServiceLevels { get; set; }
        // public ICollection<Request> Requests { get; set; }
        ////public ICollection<Request> RequestsCouffier { get; set; }
        //public virtual ICollection<Ticket> Tickets { get; set; }
        public virtual ICollection<Rating> UserRatings { get; set; }
        //public virtual ICollection<Rating> UserRatingsBy { get; set; }
        //public ICollection<IdentityRole> Roles { get; set; }

        //Roles = new Collection<IdentityUserRole>();
        //#endregion
}

当我叫它时，我得到了错误：

When I call it, I get this error:

消息：调用存储过程时不支持Include操作。

源：Microsoft.EntityFrameworkCore .relational

Message: The Include operation is not supported when calling a stored procedure.
Source: Microsoft.EntityFrameworkCore.Relational

推荐答案

您可以通过DbContext.Entry（...）API显式加载导航属性。 https://docs.microsoft.com/ zh-cn / ef / core / querying / related-data＃explicit-loading

You can explicitly load a navigation property via the DbContext.Entry(...) API. https://docs.microsoft.com/en-us/ef/core/querying/related-data#explicit-loading

public IQueryable<ApplicationUser> SelectNearByUser()
{
    var query = "execute dbo.GetCouffierSearch";
    var res = _context.Users.FromSql(query);
    _context.Entry(res)
            .Collection(x => x.CoiffeurServiceLevels)
            .Load();
    return res;
}

这篇关于使用FromSql和include时出错（“调用存储过程时不支持Include操作。”）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！