How to query a view with child table collection?

Question

I want to query a view with with child table collection in EF Core 3.1. Take my simplified example with 3 tables:

public class Relation
{
    public long Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class Invoice
{
    public long Id { get; set; }
    public string Reference { get; set; }

    public long RelationId { get; set; }
    public virtual Relation Relation { get; set; }

    public virtual ICollection<InvoiceLine> InvoiceLines { get; set; } = new HashSet<InvoiceLine>();
}

public class InvoiceLine
{
    public long Id { get; set; }
    public decimal Amount { get; set; }
    public decimal Price { get; set; }
    public string ArticleReference { get; set; }

    public long InvoiceId { get; set; }
    public virtual Invoice Invoice { get; set; }
}

I add the SQL view to my migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"
        CREATE OR ALTER VIEW dbo.InvoiceOverview
        AS
        SELECT 
            i.Id
            ,i.Reference
            ,r.FistName + ' ' + r.LastName as Name
        FROM dbo.Invoices i
        INNER JOIN Relations r ON r.Id = i.RelationId");
}

InvoiceView model + Config

public class InvoiceView
{
    public long Id { get; set; }
    public string Reference { get; set; }
    public string Name { get; set; }
}

public void Configure(EntityTypeBuilder<InvoiceOverview> builder)
{
    builder.HasKey(_ => _.Id);
    builder.ToView("InvoiceOverview");
}

So far so good. All the above works but I want to be able to query on invoice lines like this:

Context.InvoiceOverview.AsNoTracking().AsQueryable().Where(_ =>
    _.InvoiceLines.Select(invoiceLine => invoiceLine.ArticleReference)
        .Any(articleReference => articleReference == "Test").ToListAsync();

I added InvoiceLine-collection to the InvoiceOverview model and update the config.

public class InvoiceView
{
    public long Id { get; set; }
    public string Reference { get; set; }
    public string Name { get; set; }

    public virtual ICollection<InvoiceLine> InvoiceLines { get; set; }
}

public void Configure(EntityTypeBuilder<InvoiceOverview> builder)
{
    builder.HasKey(_ => _.Id);
    builder.ToView("InvoiceOverview");

    builder.HasMany(_ => _.InvoiceLines)
        .WithOne()
        .HasForeignKey(_ => _.InvoiceId);
}

With the above configuration, I successfully can run the query. The only problem I now have is, when I run 'Add-Migration', they want to create a foreign key. It's not possible to create a foreign key to a view (and the foreign key already exists on the table used in the view).

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddForeignKey(
        name: "FK_InvoiceLines_InvoiceOverview_InvoiceId",
        table: "InvoiceLines",
        column: "InvoiceId",
        principalTable: "InvoiceOverview",
        principalColumn: "Id",
        onDelete: ReferentialAction.Cascade);
}

Is it possible to explicit ignoring the foreign key while generating the migration script or should I write my configuration differently?

Answer 1

I found a solution in Entity Framework Core 2.2 : Disable migrations for specific entities

public void Configure(EntityTypeBuilder<InvoiceOverview> builder)
{
    builder.HasKey(_ => _.Id);
    builder.ToView("InvoiceOverview");

    builder.HasMany(_ => _.InvoiceLines)
        .WithOne()
        .HasForeignKey(_ => _.InvoiceId);

    if (MigrationHelper.IsMigrationOperationExecuting())
    {
        builder.Ignore(x => x.InvoiceLines);
    }
}

public static class MigrationHelper
{
    public static bool IsMigrationOperationExecuting()
    {
        var commandLineArguments = Environment.GetCommandLineArgs();
        string[] orderedMigrationArguments = { "migrations", "add" };

        for (var i = 0; i <= commandLineArguments.Length - orderedMigrationArguments.Length; i++)
        {
            if (commandLineArguments.Skip(i).Take(orderedMigrationArguments.Length).SequenceEqual(orderedMigrationArguments))
                return true;
        }

        return false;
    }
}