Enable flexible FromSql mapping

[AndriySvyryd]

trafficstars

This builds upon https://github.com/dotnet/efcore/issues/17063, https://github.com/dotnet/efcore/issues/13358 to enable different mappings for different SQL queries and to allow parameter configuration. This would also support JSON columns.

It would be configured like so:

modelBuilder
  .Entity<Blog>()
  .HasQuerySql("GetDeletedBlogs", "select id as BlogId, name as BlogName from Blogs where Deleted={0}",
    q =>
    {
        q.Propety(b => b.Id).ToColumn("BlogId"));
        q.Propety(b => b.Name).ToColumn("BlogName"));
        q.Parameter("0").HasType("bit"));
    });

And used like so:

var deletedBlogs = context.Set<Blog>().FromNamedSql("GetDeletedBlogs", true).ToList();

or

var deletedBlogs = context.Set<Blog>().FromNamedSqlRaw("GetDeletedBlogs",
  "select id as BlogId, name as BlogName from Blogs where Deleted={0}",
  true)
 .ToList();

[ircnelson]

In your second example, you did mean?

var deletedBlogs = context.Set<Blog>().FromNamedSqlRaw("GetDeletedBlogs",
  "select id, name from Blogs where Deleted={0}",
  true)
 .ToList();

I suppose that Blog class have Id and Name properties, and should be filled by convection through sql fields and class property's names, correct?

[AndriySvyryd]

@ircnelson No, the example shows that column names can be customized for raw SQL queries without affecting the table to which the entity type is also mapped.

[AndriySvyryd]

Also add a note to the CustomQueryMappingOnOwner exception suggesting to use named queries

[smitpatel]

Also consider API to mark the SQL as non-composable so we would run it as-is.

[AndriySvyryd]

Related: https://github.com/dotnet/efcore/issues/21827, #21660