Does EF core's HasIndex(Expression<Func<T,object>>) always get the index ordering correct?

user_1410664 0
Viewed 259

As far as I'm aware, there isn't a reliable, documented way to get anonymous type properties in the order that they're declared in a source file which causes me to wonder if I use EF core's HasIndex thus:

modelBuilder.Entity<T>(entity => entity.HasIndex(e => new { e.Z, e.A }) )

..is it certain that the index will be created in column order Z,A ?

I'm less concerned about the params string overload form:

modelBuilder.Entity<T>(entity => entity.HasIndex("Z", "A") )

..because I imagine it would be logical for the array element order to dictate the index column order.

I'm struggling to use this form, however, without hardcoded strings because the DbSet<X> is defined thus:

public virtual DbSet<X> X {get;set;}

..rather than the plural Xs (not my rule, but I'm stuck with it), so trying to use HasIndex(nameof(X.Z), nameof(X.A)) is an error because the nearest accessible X is a collection of X, rather than type X, and hence doesn't have the properties I want to nameof

The closest I've been able to come to work around this issue is to instantiate an X:

modelBuilder.Entity<SessionChargingProfileLog>(entity =>
{
    var x = new X(0, 0, "");

    entity.HasIndex(nameof(x.Z), nameof(x.A)).IsClustered().IncludeProperties(nameof(x.B));

});

..which is a bit..

So if it could be concretely confirmed that "yes, the HasIndex(e => new { e.Z, e.A }) will definitely create the index as Z, A" it'd be be marvellous; I'd test it, but I don't think "try it and observe if it's right in this case" means that it guarantees it will always work out, versus a "yes, it'll work because.."

ef-core-3.1 c#
2 Answers

As far as I'm aware, there isn't a reliable, documented way to get anonymous type properties in the order that they're declared in a source file

You are missing the fact that here you are not dealing with anonymous type at runtime via reflection, but with compile time generated expression tree representing anonymous type instantiation. The body of the lambda is NewExpression (not MemberInit as it looks syntactically), which is a constructor call with Arguments containing the defining expressions in the order you specify them and also mapped to Members which is specifically made for anonymous types:

The Members property provides a mapping between the constructor arguments and the type members that correspond to those values. In the case of the construction of an anonymous type, this property maps the constructor arguments to the properties that are exposed by the anonymous type. This mapping information is important because the fields that are initialized by the construction of an anonymous type, or the properties that access those fields, are not discoverable through the Constructor or Arguments properties of a NewExpression node.

What about the order, the documentation for Anonymous Types says:

If two or more anonymous object initializers in an assembly specify a sequence of properties that are in the same order and that have the same names and types, the compiler treats the objects as instances of the same type. They share the same compiler-generated type information.

So since the order of the initialization is part of the anonymous type identity, then it should be preserved by the complier, and in turn reflected in the compiler generated lambda expression.

user_52025630

Yes, EF creates index in the same order as you defined, exactly.

Because the order of columns in index is so important in relational databases and all search scenarios are in order of columns for seek indexes (when build the execution plan).

As you know when change the order of columns for specific index you should rebuild that index for create based on new order.

user_60231730
Related