Issue: Performance while working with EF core

[acmajia]

Recently, I want to use dynamic linq in my aspnet core web application, to dymaniclly build my query.

So I want to test the performance of dynamic linq while working with ef core.

In my test, I use EF core Inmemory database to store my data, then query the data by origin linq expression and dynamiclinq, and compare the time they cost.

Here is my codes:

var list = new List<Person>();

list.Add(new Person
{
	Age = 1,
	EnglishName = "sadfasdf",
	Id = Guid.NewGuid(),
	Name = "tom"
});

list.Add(new Person
{
	Age = 2,
	EnglishName = "ddddd",
	Id = Guid.NewGuid(),
	Name = "jerry"
});

var sw = Stopwatch.StartNew();

using (var context = new TestContext())
{
	context.Set<Person>().AddRange(list);
	context.SaveChanges();
}

for (var i = 0; i <= 1_000; i++)
{
	using (var context = new TestContext())
	{
		var set = context.Set<Person>().Where(m => m.Age == 1 && m.Name == "tom").ToList();
	}
}

Console.WriteLine(sw.Elapsed);
sw.Restart();

for (var i = 0; i <= 1_000; i++)
{
	using (var context = new TestContext())
	{
		var set = context.Set<Person>().Where($"$.Age==@0 and $.Name==@1", 1, "tom").ToList();
	}
}

Console.WriteLine(sw.Elapsed);
Console.ReadLine();

As you can see, in my codes, I looped 1000 times to compare the query performance between origin linq expression and dynamic linq, and here is my console outputs:

00:00:01.2343062 -> origin linq expression query 1000 times
00:00:09.7961837 -> dynamic linq query 1000 times

It seems that dynamic linq is much more slower than origin linq expression while working with ef core.

Can anybody help me? Did I missed someting about dynamic linq?

Thank you.

[StefH]

Generating a dynamic class takes some time, but this should only be done once I hope.

Can you create a full example project ?

[acmajia]

Hello, StefH. Thanks for the reply!

And I'm sorry about the previous test result.
When I ran the previous test, I did'n close my Visual Studio diagnostic tool.
And when I ran it by ctrl + F5 (without the performance monitoring),
here is my console outputs:

00:00:16.8311778 -> origin linq expression query 100_000 times
00:00:43.1919448 -> dynamic linq query 100_000 times

And this is my git repository:
https://github.com/acmajia/DynamicLinqWithEfCoreTest

Again, thanks for the help!

[StefH]

I found the code which has the most performance impact, it's ExpressionParser.cs. I will take a look if I can cache this.

[jogibear9988]

It's obvious that it will take more time. With dynamic linq you create an expression tree at runtime, wich is then used against the queryable. With your other example, the expression tree is created at compile time.

But you can use ExpressionParser directly, and store the expression and then use the cached expression in the where.

[acmajia]

Hello, @jogibear9988 .

I found ExpressionParser is internal, so I used DynamicExpressionParser to compile my expression. It is just like below:

DynamicExpressionParser.ParseLambda(typeof(Person), null, $"$.Age==@0 and $.Name==@1", 1, "tom");

In this way, I can solve part of my problems, but my query parameter values are dynamic. That means, I may query $.Age==1 and $.Name==tom or $.Age==2 and $.Name==jerry, and it seems unreasonable to cache them all.

Again, thank you!

[jogibear9988]

I don‘t think the cache should be in dynamic linq. If it is, how big it should be,... I won‘t like it.

I think we should be able to return a Compiled Expression Tree, to wich you can hand over the paramters later.

I‘ll look if this is possible. But don‘t know when I‘ve time! And maybe you still need to pass the first parameters, cause somtimes the type of the parameter is needed to create the tree!

[StefH]

With some changes I get on my machine this performance:

00:00:29.3522098
00:00:39.1181930

[StefH]

New nuget will be uploaded.