preface
I haven't written a blog for a long time. Let's make up for an article today. I occasionally find that I'm not willing to write a blog, which takes too much time, but I'm still persevere. After all, maybe the things I write can help some children's shoes. Next, let's go straight to the point. Whether it is in EF 6.x or EF Core, the APi for the original query are relatively weak. For example, we only want to query a single value, but they are not supported. For example, we only want some columns, but they are also not supported. Too many are not supported. The only support is that only all columns in the table, that is, all fields in the class, can be returned. So in most cases, I write native SQL, and I haven't used the original query much. Recently, a colleague who loves EF asked me how to use SqlQuery to implement dynamic queries. I have no answer and never thought about using this method. After reading it privately, I still want to give a little thought. It's great if it's helpful to you. If it doesn't work, just treat it as a blog post I posted.
EF 6.x and EF Core implement dynamic queries
public static IEnumerable<dynamic> SqlQueryDynamic(this DbContext db, string Sql, params SqlParameter[] parameters)
{
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = Sql;
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
foreach (var p in parameters)
{
var dbParameter = cmd.CreateParameter();
dbParameter.DbType = p.DbType;
dbParameter.ParameterName = p.ParameterName;
dbParameter.Value = p.Value;
cmd.Parameters.Add(dbParameter);
}
using (var dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
var row = new ExpandoObject() as IDictionary<string, object>;
for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
{
row.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
}
yield return row;
}
}
}
}
So how should we convert the dynamic collection into a collection object after the query above? I didn't even think of directly serializing first and then deserializing as follows. If you have a better solution, please implement it yourself.
using (var ctx = new EfDbContext())
{
ctx.Database.Log = Console.WriteLine;
var dynamicOrders = ctx.SqlQueryDynamic("select * from dbo.Orders");
var ordersJson = JsonConvert.SerializeObject(dynamicOrders);
var orders = JsonConvert.DeserializeObject<List<Order>>(ordersJson);
};
Of course, I just simply queried one table above. It would be useful if you have multiple tables. Finally, you can deserialize them into different objects. You can test it yourself without testing.
EF Core uses multiple context instance pools
Many people have always used one context, whether in EF 6.x or EF Core, but have we ever thought of using multiple contexts? For example, in e-commerce projects, we can use product context for product-related operations, shopping cart context for adding shopping cart operations, and order context for order operations. What are the benefits of doing this? We can split database tables, that is, entities, into different services. I haven't seen anyone do this yet, and if it were me, at least I would do it.
//Add DbContext
var dbConnetionString = Configuration.GetConnectionString("DbConnection");
services.AddDbContextPool<ShopCartDbContext>(options =>
{
options.UseSqlServer(dbConnetionString);
}).AddDbContextPool<BookDbContext>(options =>
{
options.UseSqlServer(dbConnetionString);
}).AddDbContextPool<OrderDbContext>(options =>
{
options.UseSqlServer(dbConnetionString);
});
There is a context instance pool in EF Core 2.0, which is similar to the connection pool in ADO.NET. But if you understand it on the surface, you are wrong. Regarding the implementation nature of the context instance pool (I started writing a book on EF 6.x and EF Core that will be published soon since last year), I can only say that it is not the same as the connection pool in ADO.NET. So will it always work to use multiple context instance pools as above? Sorry, this configuration is wrong. But when running the program, you will find an exception similar to the following:
Exception message:
System.ArgumentException: Expression of type 'Microsoft.EntityFrameworkCore.DbContextOptions`1[MultiContext.Contexts.BContext]' cannot be used for constructor parameter of type 'Microsoft.EntityFrameworkCore.DbContextOptions`1[MultiContext.Contexts.AContext]' Parameter name: arguments[0]
Stack trace:
...........
When this feature came out, everyone was cheering for improving performance. Sorry, although context instance pools may improve performance to a certain extent, I can only say that there are only possible performance improvements. If you know or have seen EF Core implementing context instance pools, you will understand the nature of its implementation and realize what I mean by possible performance improvements. As for why multiple context instance pools cannot be registered, I also encountered it privately when writing a project. For details, please see github:
https://github.com/aspnet/EntityFrameworkCore/issues/9433。
summary
Okay, that's it for today. Without too much explanation or narration, I just get straight to the point. Recently, while I was flying in my thoughts, I slowly lost a lot of interest in blogging. Occasionally, I will wait until I am full of blood and adjust my mood. I will continue to share technology with you. I have been there. Maybe I am tired, or I have been studying Identity Server or other technologies privately and doing our job. Unless you change careers, then you should honestly accumulate experience and learn more skills. If you don't struggle when you are young, when will you struggle? What did you say today? Don't take offense in your imagination.
What you see is not the thing itself, but the meaning it gives it after interpretation