How can i use Custom query Single field in EFCore？

user_13766847 0
Viewed 100
  1. In EF6, there is context.Database.sqlquery that I can use. Why does EF Core cancel this?
  2. I try to find the answer but the context.user.FromInterpolated doesn't allow select Single field. (Or am I using the wrong method of this?)
  3. Here is my code. Please give me some advice that I can solve this problem.
[HttpPost]
[Obsolete]
public async Task<IActionResult> SendEmail()
{
    Email email = new Email();
    var ID = HttpContext.Request.Form["ID"].ToString();
    var Title = HttpContext.Request.Form["Title"].ToString();
    var Body = HttpContext.Request.Form["Body"].ToString();
    var Emails = context.user.FromSqlInterpolated($"select email from user where UserId in({ID})");
    foreach (var item in Emails)
    {
        if (!string.IsNullOrEmpty(item.Email))
        {
            email.Send(item.Email, Title, Body);
        }
    }
    await HttpResponseWritingExtensions.WriteAsync(this.Response, "success");
    return RedirectToAction(nameof(Index));
}
asp.net-core-mvc ef-core-3.1 asp.net-core .net-core entity-framework
2 Answers

You can do something like this:

public class StringReturn
{
   public string Value { get; set; }
}

partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<StringReturn>().HasNoKey();
}

using (var db = new NorthwindContext())
{
    var result = db.Set<IntReturn>()
      .FromSqlRaw("exec dbo.Scalar")
      .AsEnumerable()
      .First().Value;
    
    Console.WriteLine(result);
}

See my blog post here for more info: https://erikej.github.io/efcore/2020/05/26/ef-core-fromsql-scalar.html

user_1839340

Ok first of all you have sql injection in your code. So if user pass in field id smth like 1 ) or ( 1=1 . It will allow it and that is basic not very harmfull in your case but

What you should really do is

var ids= HttpContext.Request.Form["ID"].ToString().Split(",", StringSplitOptions.RemoveEmptyEntries).ToList();
if(ids.Any()){
   var Emails = await context.user.Where(u=>ids.Contains(u.UserId)).Select(u=>u.email).ToListAsync();
}
user_11002480
Related