Query on owned entity produces overly complicated SQL

[matteocontrini]

When querying an entity and filtering on an owned entity the SQL query that is produced includes a LEFT JOIN that could be avoided.

Steps to reproduce

Entites:

class Order
{
    public int Id { get; set; }
    public string Title { get; set; }
    public Address Address { get; set; }
}

class Address
{
    public string Street { get; set; }
    public string City { get; set; }
}

Model configuration:

modelBuilder.Entity<Order>().OwnsOne(x => x.Address);

The database table that is created looks like this:

A simple query like:

context.Orders.Where(x => x.Address == null).ToList();

Produces this SQL:

SELECT o."Id", o."Title", t."Id", t."Address_City", t."Address_Street"
      FROM "Orders" AS o
      LEFT JOIN (
          SELECT o0."Id", o0."Address_City", o0."Address_Street", o1."Id" AS "Id0"
          FROM "Orders" AS o0
          INNER JOIN "Orders" AS o1 ON o0."Id" = o1."Id"
          WHERE (o0."Address_Street" IS NOT NULL) OR (o0."Address_City" IS NOT NULL)
      ) AS t ON o."Id" = t."Id"
      WHERE (t."Id" IS NULL)

Which is overly complicated. The columns Address_City and Address_Street are available on the Orders table without any JOIN.

Same thing when querying a specific owned entity property:

context.Orders.Where(x => x.Address.City == "Rome").ToList();

SELECT o."Id", o."Title", t."Id", t."Address_City", t."Address_Street"
      FROM "Orders" AS o
      LEFT JOIN (
          SELECT o0."Id", o0."Address_City", o0."Address_Street", o1."Id" AS "Id0"
          FROM "Orders" AS o0
          INNER JOIN "Orders" AS o1 ON o0."Id" = o1."Id"
          WHERE (o0."Address_Street" IS NOT NULL) OR (o0."Address_City" IS NOT NULL)
      ) AS t ON o."Id" = t."Id"
      WHERE (t."Address_City" = 'Rome') AND (t."Address_City" IS NOT NULL)

Further technical details

Example project (PostgreSQL): EfCoreOwnedEntity.zip

EF Core version: 3.0.0
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL 3.0.1
Target framework: .NET Core 3.0
Operating system: Windows 10 1903
IDE: e.g. Visual Studio 2019 16.3.2

[ajcvickers]

@smitpatel to investigate.

[smitpatel]

Legit generated SQL.

[AndriySvyryd]

@smitpatel I think in these cases we could get rid of the join since the outer filter is more restrictive. However we would need to first profile whether this would result in any measurable perf improvement besides just being a simpler query.

[salaros]

Legit generated SQL.

@smitpatel Not if you are not using owned entities with table splitting.

For example we are using owned entity for audit-related information

[Owned]
public class AuditLog
{
    [Column(nameof(IsDeleted), Order = 990)]
    public bool IsDeleted { get; set; }

    [Column(nameof(CreatedTime), Order = 991)]
    public DateTime CreatedTime { get; set; }

    [Column(nameof(ModifiedTime), Order = 992)]
    public DateTime? ModifiedTime { get; set; }

    [Column(nameof(CreatedBy), Order = 993)]
    public string CreatedBy { get; set; }

    [Column(nameof(ModifiedBy), Order = 994)]
    public string ModifiedBy { get; set; }
}

We put this entity on many multiple entities (e.g. manufacturers, products, product translations etc), therefore our EF Core-generated queries are monstrous.

This simple expression

var mfgsWithProducts = dbContext
              .Set<Manufacturer>()
              .Include(m => m.Products)
              .ThenInclude(p => p.Translations)
              .ToList();

results in

SELECT ....
FROM [Manufacturers] AS [m]
LEFT JOIN (
    SELECT ....
    FROM [Manufacturers] AS [m0]
    INNER JOIN [Manufacturers] AS [m1] ON [m0].[Id] = [m1].[Id]
    WHERE [m0].[IsDeleted] IS NOT NULL AND ([m0].[CreatedTime] IS NOT NULL AND [m0].[CreatedBy] IS NOT NULL)
) AS [t] ON [m].[Id] = [t].[Id]
LEFT JOIN (
    SELECT ....
    FROM [Products] AS [p0]
    LEFT JOIN (
        SELECT ....
        FROM [Products] AS [p1]
        INNER JOIN [Products] AS [p2] ON [p1].[Id] = [p2].[Id]
        WHERE [p1].[IsDeleted] IS NOT NULL AND ([p1].[CreatedTime] IS NOT NULL AND [p1].[CreatedBy] IS NOT NULL)
    ) AS [t0] ON [p0].[Id] = [t0].[Id]
    LEFT JOIN (
        SELECT ....
        FROM [ProductTranslations] AS [p3]
        LEFT JOIN (
            SELECT ....
            FROM [ProductTranslations] AS [p4]
            INNER JOIN [ProductTranslations] AS [p5] ON [p4].[Id] = [p5].[Id]
            WHERE [p4].[IsDeleted] IS NOT NULL AND ([p4].[CreatedTime] IS NOT NULL AND [p4].[CreatedBy] IS NOT NULL)
        ) AS [t1] ON [p3].[Id] = [t1].[Id]
    ) AS [t2] ON [p0].[Id] = [t2].[ProductId]
) AS [t3] ON [m].[Id] = [t3].[ManufacturerId]
ORDER BY [m].[Id], [p].[ManufacturerId], [p].[Id], [t3].[Id], [t3].[Id1]

this is ridiculous, since we use owned entities without table splitting, therefore we don't need to left join table to themselves

[salaros]

Any news?

[ajcvickers]

@salaros This issue is in the Backlog milestone. This means that it is not going to happen for the 3.1 release. We will re-assess the backlog following the 3.1 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[dahumadaatgmail]

any workaround?
I have extensive use of Owned entities and the query with left joins on the same table generates time-out because of the complexity. It could be a simple select * but EF generates 10 left join queries on the same table. I tried to use FromSqlRaw but the result is just another join

[salaros]

any workaround?

for now we stopped using owned entities

[dahumadaatgmail]

Because of the extensive use of owned entities, I can't stop using it. As a workaround I built a database view as a plain object and I referenced the view from my context. By now this has been working (the real Order entity has more owned entities than showed):

Entities:

public class Order{
   public string Number{get;set;}
   public Person BillTo{get;set;}
   public Person InvoicedTo{get;set;}
}

[Owned]
public class Person {
  public string TaxID{get;set;}
  public string Name{get;set;}
  public string Address{get;set;}
}

this is the table:

create table Orders (
  Id varchar(100)
  BillTo_TaxID varchar(100)
  BillTo_Name varchar(100)
  BillTo_Address varchar(100)
  InvoicedTo_TaxID varchar(100)
  InvoicedTo_Name varchar(100)
  InvoicedTo_Address varchar(100)
)

Now, I created a view from my table:

create view VwOrders as select * from dbo.Orders;

And in my dbContext I added the view as an Entity:

public DbSet<VwOrder> VwOrders { get; set; }

And in the builder:

modelBuilder.Entity<VwOrder>(eb => {
                eb.HasNoKey();
                eb.ToView("VwOrders");
            });

The VwOrder class:

public class VwOrder {
    public string Id {get;set;}
    public string BillTo_TaxID {get;set;}
    public string BillTo_Name {get;set;}
    public string BillTo_Address {get;set;}
    public string InvoicedTo_TaxID {get;set;}
    public string InvoicedTo_Name {get;set;}
    public string InvoicedTo_Address {get;set;}
    public Order ToOrder(){
        var ret = new Order {
            Id = this.Id,
            BillTo = createEntity<Person>("BillTo"),
            InvoicedTo = createEntity<Person>("InvoicedTo")
        }
        return ret;
    }
}

And the method createEntity:

        private T createEntity<T>(string prefix) where T : new() {
            var datos = new T();
            var myprops = this.GetType().GetProperties().Where(x => x.CanRead).ToDictionary(x => x.Name);
            var props = datos.GetType().GetProperties().Where(x => x.CanWrite).ToArray();
            foreach(var prop in props) {
                try {
                    prop.SetValue(datos, myprops[prefix + "_" + prop.Name].GetValue(this));
                } catch(Exception ex) {
                    Console.WriteLine($"{ex}");
                }
            }
            return datos;
        }

To query, I just made something like:

var order = bd.VwOrders.Where(x=>x.Id == "xx").AsNoTracking().FirstOrDefault()?.ToOrder();

[chapinmark]

@salaros

Did you find that the nested joins hurt your query performance? Mine went from nearly negligible (around 1ms) with ef core 2.2 to 1.5 seconds with ef core 3.0 (200k rows in the table).

[salaros]

Did you find that the nested joins hurt your query performance? Mine went from nearly negligible (around 1ms) with ef core 2.2 to 1.5 seconds with ef core 3.0 (200k rows in the table).

Yeap, especially with orderby and global filters, but as they say

Legit generated SQL

I'm planning to create a PR ASAP with a fix for owned entities (without table splitting). Unfortunately EF Core's release policies are very strange, so there is no way to tell if it's gonna make it for 3.1. In general it seems like the main goal is to gradually kill this project.

[smitpatel]

Legit generated SQL.

Just to be clear for everyone going in tangential direction. My comment says in other words, "yes, we generated that SQL & we should fix it".

so there is no way to tell if it's gonna make it for 3.1

Not just EF Core, any other open source project on github, look at the milestone of the issue tracking any item. The milestone will tell which release it was or will be fixed. This issue is in backlog milestone and it is not going to happen for 3.1. Release 3.1 is already finalized and going through testing phase.

As for submitting a fix for this issue, this issue is not marked as "good-first-issue" hence we believe it is fairly complex issue. We still encourage you to work on it if you wish. But make sure to discuss design of the fix with us first (by commenting in this issue). If you submit PR directly and if that is incorrect fix, we will not accept the contribution.

[ZimM-LostPolygon]

Inefficient SQL is one thing. But is there a reason for EF to generate non-nullable owned entity property columns as nullable? If I have something like this:

public class Device {
    [Key]
    public string Id { get; set; }
    
    [Required]
    public DeviceBasicStatistics BasicStatistics { get; set; }
}

public class DeviceBasicStatistics {
    // long is not nullable, yet the column is generated as nullable
    public long ReportCount { get; set; }
}

I would expect ReportCount to default to 0, and since 0 is not null, BasicStatistics will always be created. However, BasicStatistics_ReportCount is generated as nullable, and if the ReportCount is set to null for whatever reason, Device.BasicStatistics is not loaded and remains null, which breaks the expectations.

Is this a separate issue?

[ajcvickers]

@ZimM-LostPolygon See #12100

[MorenoGentili]

I think this should be marked as a type-bug instead of a type-enhancement. In fact, as more rows are added to a table, performance progressively degrades to the point it becomes unusable. Users could not be fully aware of this problem; maybe Microsoft should issue an official statement to discourage using owned types in EFCore 3.0.

My model is identical to @matteocontrini's except for the fact it has 2 owned type properties in my entity class instead of just 1. Here's the query generated by EFCore. It's way too complicated: there are LEFT JOINs of subqueries with nested INNER JOINs.

SELECT "t"."Id", "t"."Author", "t"."Description", "t"."Email", "t"."ImagePath", "t"."Rating", "t"."Title", "t2"."Id", "t2"."CurrentPrice_Amount", "t2"."CurrentPrice_Currency", "t1"."Id", "t1"."FullPrice_Amount", "t1"."FullPrice_Currency"
FROM (
    SELECT "c"."Id", "c"."Author", "c"."Description", "c"."Email", "c"."ImagePath", "c"."Rating", "c"."Title"
    FROM "Courses" AS "c"
    WHERE ((@__model_Search_0 = '') AND @__model_Search_0 IS NOT NULL) OR (instr("c"."Title", @__model_Search_0) > 0)
    ORDER BY "c"."Rating" DESC
    LIMIT @__p_2 OFFSET @__p_1
) AS "t"
LEFT JOIN (
    SELECT "c0"."Id", "c0"."CurrentPrice_Amount", "c0"."CurrentPrice_Currency", "c1"."Id" AS "Id0"
    FROM "Courses" AS "c0"
    INNER JOIN "Courses" AS "c1" ON "c0"."Id" = "c1"."Id"
    WHERE "c0"."CurrentPrice_Currency" IS NOT NULL AND "c0"."CurrentPrice_Amount" IS NOT NULL
) AS "t0" ON "t"."Id" = "t0"."Id"
LEFT JOIN (
    SELECT "c2"."Id", "c2"."FullPrice_Amount", "c2"."FullPrice_Currency", "c3"."Id" AS "Id0"
    FROM "Courses" AS "c2"
    INNER JOIN "Courses" AS "c3" ON "c2"."Id" = "c3"."Id"
    WHERE "c2"."FullPrice_Currency" IS NOT NULL AND "c2"."FullPrice_Amount" IS NOT NULL
) AS "t1" ON "t"."Id" = "t1"."Id"
LEFT JOIN (
    SELECT "c4"."Id", "c4"."CurrentPrice_Amount", "c4"."CurrentPrice_Currency", "c5"."Id" AS "Id0"
    FROM "Courses" AS "c4"
    INNER JOIN "Courses" AS "c5" ON "c4"."Id" = "c5"."Id"
    WHERE "c4"."CurrentPrice_Currency" IS NOT NULL AND "c4"."CurrentPrice_Amount" IS NOT NULL
) AS "t2" ON "t"."Id" = "t2"."Id"
ORDER BY "t"."Rating" DESC

And here's a quick benchmark I performed. The blue line represents a SQL query I typed by hand and the orange line is the query generated by the LINQ provider. As you can see, performance starts degrading very fast as more rows are added to the table. I'm talking about just 2000 rows in a Sqlite database. All needed indexes are in place.

[VaclavElias]

I am experiencing the same problem, was happy to use Owned entities till I realised that I have got 4 left joins to the same table. I am not going to use them, till this is fixed.

[msneijders]

If you use Nested owned types it gets a lot worse.

If you extend the model to:

    class Order
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public Address Address { get; set; }
    }

    [Owned]
    class Address
    {
        public string Street { get; set; }
        public string City { get; set; }
        public PostalCode PostalCode { get; set; }
    }

    [Owned]
    class PostalCode
    {
        public string Area { get; set; }
        public string Zone { get; set; }
    }

then

context.Orders.ToList();

produces (postgresql):

SELECT o."Id", o."Title", t1."Id", t1."Address_City", t1."Address_Street", t5."Id", t5."Address_PostalCode_Area", t5."Address_PostalCode_Zone"
FROM "Order" AS o
LEFT JOIN (
    SELECT t0."Id", t0."Address_City", t0."Address_Street", o3."Id" AS "Id0"
    FROM (
        SELECT o0."Id", o0."Address_City", o0."Address_Street"
        FROM "Order" AS o0
        WHERE (o0."Address_Street" IS NOT NULL) OR (o0."Address_City" IS NOT NULL)
        UNION
        SELECT o1."Id", o1."Address_City", o1."Address_Street"
        FROM "Order" AS o1
        INNER JOIN (
            SELECT o2."Id", o2."Address_PostalCode_Area", o2."Address_PostalCode_Zone"
            FROM "Order" AS o2
            WHERE (o2."Address_PostalCode_Zone" IS NOT NULL) OR (o2."Address_PostalCode_Area" IS NOT NULL)
        ) AS t ON o1."Id" = t."Id"
    ) AS t0
    INNER JOIN "Order" AS o3 ON t0."Id" = o3."Id"
) AS t1 ON o."Id" = t1."Id"
LEFT JOIN (
    SELECT o4."Id", o4."Address_PostalCode_Area", o4."Address_PostalCode_Zone", t4."Id" AS "Id0", t4."Id0" AS "Id00"
    FROM "Order" AS o4
    INNER JOIN (
        SELECT t3."Id", t3."Address_City", t3."Address_Street", o8."Id" AS "Id0"
        FROM (
            SELECT o5."Id", o5."Address_City", o5."Address_Street"
            FROM "Order" AS o5
            WHERE (o5."Address_Street" IS NOT NULL) OR (o5."Address_City" IS NOT NULL)
            UNION
            SELECT o6."Id", o6."Address_City", o6."Address_Street"
            FROM "Order" AS o6
            INNER JOIN (
                SELECT o7."Id", o7."Address_PostalCode_Area", o7."Address_PostalCode_Zone"
                FROM "Order" AS o7
                WHERE (o7."Address_PostalCode_Zone" IS NOT NULL) OR (o7."Address_PostalCode_Area" IS NOT NULL)
            ) AS t2 ON o6."Id" = t2."Id"
        ) AS t3
        INNER JOIN "Order" AS o8 ON t3."Id" = o8."Id"
    ) AS t4 ON o4."Id" = t4."Id"
    WHERE (o4."Address_PostalCode_Zone" IS NOT NULL) OR (o4."Address_PostalCode_Area" IS NOT NULL)
) AS t5 ON t1."Id" = t5."Id"

Indication of performance problem (tested using different model, but equivalent), using a table with 40,000 records:

Efcore 3.1 query: 500 ms
Manual query: 100 ms.

If you use a Where filter, the performance difference gets a lot bigger. A filter selecting only 2 records (using index) from the table:

Efcore 3.1 query: 280ms
Manual query: 1ms.

This makes the owned entity with table splitting feature not useful in practice.

[julielerman]

@AndriySvyryd ahh when inheritance is involved... got it. Glad you were at least able to find some places where you could fix it pre-EFCore 5.

[ajcvickers]

We believe this issue is fixed (except maybe some edge cases) in EF Core 5.0 preview 3, which is now live on NuGet. See the announcement post for full details.

Please, please try this preview and let us know if you're still running into issues so that we can address any remaining issues in future 5.0 previews.

Note that .NET 5 is not required to use EF 5.0. You can try it with your .NET Core 3.1 applications!

[cbordeman]

This is fixed in the latest .net 5.0 preview.

[Vake93]

Is there any chance the fix in EF 5 to be back ported into EF core 3.1? With these joins Owned entity types isn’t usable at all with EF core 3.1. And since .NET core 3.1 is a LTS version we can’t really think of upgrade until the next LTS version becomes available.

[zornwal]

I know this is late, but maybe consider getting this listed as a breaking change on the EF Core 3 docs?

We're having roughly the same setup as in #18299 (comment), and simply calling
_dataContext.Users.Find(userId); where Users own another entity and userId is a primary key, will throw an Invalid operation exception stating that the sequence contains multiple elements (which is obviously nonsense as that can't happen for primary keys, and also isn't the case in the database).
Going from Find(key) finding the key to throwing an exception for a key that only exists once in the database seems like a textbook definition of a breaking change.

[smitpatel]

@zornwal - If you are hitting an exception then your issue is not related to this one. There is no error thrown if you are hitting this issue.

[smitpatel]

@Vake93 - This cannot be backported to 3.1 release.

[zornwal]

@smitpatel Alright we managed to find out what was going on, and it seems at least tangentially related to the issue.
We had a base class with a collection of owned entities, from which two other classes inherited. At some point, the collection was moved down into one of the inheriting entities instead, without removing the entries referencing the now collection-less other class. This led to querying the dbset of the base class failing with the mentioned exception when the entity in question was the collection-less one, but had entries remaining in the database. Don't know if this is intended, but it seems pretty obscure anyway. Might be worth checking if issue persists on 5.0 of it isn't intended.

[smitpatel]

@zornwal - Restating, if you are running into this issue then you will not get any exception. If you are seeing an exception then your issue is not same as this. Please file a new issue with detailed repro.

[MoazAlkharfan]

FullText search is broken because it's being run on the joined data and it's throwing an exception
Cannot use a CONTAINS or FREETEXT predicate on column ......

[t0].[Property_14] and [t0].[Property_13] should be [j].[Property_14] and [j].[Property_13] for the problem to be fixed.

SELECT [j].[Id]
FROM [TABLE_A] AS [j]
LEFT JOIN (
    SELECT [j0].[Id], [j0].[Property_1], [j0].[Property_2], [j0].[Property_3], [j0].[Property_4], [j0].[Property_5]
    FROM [TABLE_A] AS [j0]
    WHERE [j0].[Property_1] IS NOT NULL
) AS [t] ON [j].[Id] = [t].[Id]
LEFT JOIN (
    SELECT [j1].[Id], [j1].[Property_6], [j1].[Property_7], [j1].[Property_8], [j1].[Property_9], [j1].[Property_10], [j1].[Property_11], [j1].[Property_12], [j1].[Property_13], [j1].[Property_14], [j1].[Property_15], [j1].[Property_16]
    FROM [TABLE_A] AS [j1]
    WHERE [j1].[Property_11] IS NOT NULL
) AS [t0] ON [j].[Id] = [t0].[Id]
WHERE (([j].[Property_22] <> CAST(1 AS bit)) AND (DATEADD(day, CAST(10.0E0 AS int), [t].[Property_1]) > CONVERT(date, GETDATE()))) AND (((([j].[Property_22] <> CAST(1 AS bit)) AND ([j].[State] = 3)) AND (FREETEXT([t0].[Property_14], 'testsearch') OR FREETEXT([t0].[Property_13], 'testsearch'))) AND (DATEADD(day, CAST(10.0E0 AS int), [t].[Property_1]) > CONVERT(date, GETDATE())))

[AndriySvyryd]

@MoazAlkharfan Please open a new issue with a small repro project

[darkflame0]

@smitpatel @ajcvickers
When I use FromSqlRaw, it still generates complex and inefficient sql

db.LiveHistory.FromSqlRaw(@"select * from ""LiveHistory""").Select(a => a.Data.RoomId).FirstOrDefault();

      SELECT t."RoomId"
      FROM (
          select * from "LiveHistory"
      ) AS l
      LEFT JOIN (
          SELECT l0."Id", l0."RoomId"
          FROM "LiveHistory" AS l0
          INNER JOIN "LiveHistory" AS l1 ON l0."Id" = l1."Id"
      ) AS t ON l."Id" = t."Id"
      LIMIT 1

db.LiveHistory.Select(a => a.Data.RoomId).FirstOrDefault();

      SELECT l."RoomId"
      FROM "LiveHistory" AS l
      LIMIT 1

[smitpatel]

@darkflame0 - When you use FromSql* API on an entity type which has owned entities, we cannot simplify the SQL easily. EF Core does not parse the SQL to look into it what you are selecting from. The owner entity can be coming from a stored procedure or a view or a custom projection. In neither of those case we can assume that owned entity will also come from there only. Even with an example like yours, instead of select * if you specified all list of columns and did not select columns for owned entities, it wouldn't work without a join. So if we tried to pull owned entity columns, it will be invalid SQL error. Hence on the safer side we generate a join. We are tracking in #21888 to improve experience around this.

[taspeotis]

EF Core does not parse the SQL to look into it what you are selecting from. The owner entity can be coming from a stored procedure ... Hence on the safer side we generate a join

This just ... breaks FromSqlRaw though? You can't put EXECUTE MyStoredProcedure into a subquery.

SELECT * FROM ( EXECUTE ... ) LEFT JOIN ...

This is a syntax error in SQL Server. There are similar limitations on other relational databases (for example FromSqlRaw("UPDATE Table SET Counter = Counter + 1 RETURNING *") in Postgres works just fine until you have owned entity).

The documentation is full of examples of using FromSqlRaw to call stored procedures.

The documentation is also wrong:

SQL Server doesn't allow composing over stored procedure calls, so any attempt to apply additional query operators to such a call will result in invalid SQL. Use AsEnumerable or AsAsyncEnumerable method right after FromSqlRaw or FromSqlInterpolated methods to make sure that EF Core doesn't try to compose over a stored procedure.

You can't do anything to prevent EF Core from composing its LEFT JOINs for owned entities, it just goes straight to breaking the query.

The owner entity can be coming from a stored procedure or a view or a custom projection. In neither of those case we can assume that owned entity will also come from there only.

The documentation already passes the obligation on to whoever is calling FromSqlRaw to get all the data:

There are a few limitations to be aware of when using raw SQL queries:

• The SQL query must return data for all properties of the entity type.

[pantonis]

I am using EF Core 5.0.5 with Sql Server and I can still see this issue. Please check my code below:

public class User
{
	[Key]
	[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
	public long Id { get; set; }

	public string Name { get; private set; }

	private readonly List<RefreshToken> refreshTokens = new List<RefreshToken>();
	public virtual IReadOnlyCollection<RefreshToken> RefreshTokens => refreshTokens;

	public User()
	{

	}

	private User(string name)
	{
		Name = name ?? throw new ArgumentNullException(nameof(name));
	}

	public static User CreateUser(string name)
	{
		return new User(name);
	}
}

public class RefreshToken : ValueObject
{
	public string Token { get; }

	public DateTime ExpirationTime { get; }

	private RefreshToken(string token, DateTime expirationTime)
	{
		Token = token;
		ExpirationTime = expirationTime;
	}

	public static Result<RefreshToken> Create(string token, int expirationMinutes)
	{
		token = (token ?? string.Empty).Trim();

		if (string.IsNullOrEmpty(token))
			return Result.Failure<RefreshToken>("Refresh Token should not be empty.");

		if (expirationMinutes <= 0)
			return Result.Failure<RefreshToken>("Invalid Expiration in minutes value.");

		DateTime expirationDate = DateTime.UtcNow.AddMinutes(expirationMinutes);

		return Result.Success(new RefreshToken(token, expirationDate));
	}

	protected override IEnumerable<object> GetEqualityComponents()
	{
		yield return Token;
		yield return ExpirationTime;
	}
}

public class MyDbContext : DbContext
{
	public MyDbContext(DbContextOptions options)
		: base(options)
	{

	}

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

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		base.OnModelCreating(modelBuilder);

		modelBuilder.Entity<User>(entity =>
		{
			entity.OwnsMany(p => p.RefreshTokens, a =>
			{
				a.ToTable("RefreshToken");
				a.WithOwner().HasForeignKey("UserId").Metadata.PrincipalToDependent.SetIsEagerLoaded(false);
				a.Property<long>("Id");
				a.HasKey("Id");

				a.Property<string>("Token").IsRequired();
				a.Property<DateTime>("ExpirationTime").IsRequired();
			});
		});
	}
}


public class UserService : IUserService
{
	private readonly MyDbContext dbContext;

	public UserService(MyDbContext dbContext)
	{
		this.dbContext = dbContext;
	}

	public void GetUsers()
	{
		var user = (from dbUser in dbContext.User
							 .Include(x => x.RefreshTokens.Where(token => token.ExpirationTime < DateTime.UtcNow))
					where dbUser.Id == 1
					select dbUser).FirstOrDefault();
	}
}

and the generated SQL query is the following:

SELECT [t].[Id], [t].[Name], [t0].[Id], [t0].[ExpirationTime], [t0].[Token], [t0].[UserId]
FROM (
  SELECT TOP(1) [u].[Id], [u].[Name]
  FROM [User] AS [u]
  WHERE [u].[Id] = CAST(1 AS bigint)
) AS [t]
LEFT JOIN (
  SELECT [r].[Id], [r].[ExpirationTime], [r].[Token], [r].[UserId]
  FROM [RefreshToken] AS [r]
  WHERE [r].[ExpirationTime] < GETUTCDATE()
) AS [t0] ON [t].[Id] = [t0].[UserId]
ORDER BY [t].[Id], [t0].[Id]

[mikke49]

We're using FromSqlRaw to access temporal tables, finding older version of entity. The entity also has owned entities. The raw SQL selects * and uses the FOR SYSTEM_TIME (something), which by itself works fine. The problem is the JOIN to fetch the owned entities, resulting in these properties having current values while all other properties are correct older version.

[pantonis]

@mikke49 I am not using FromSqlRaw.