I'm using Entity Framework Core with C# and SQL Server to run some user provided raw SQL queries, and I need to find a SQL script that will return a list of objects with a many-to-one relationship.

For example:

Models:

class Invoice 
{
    public long Id { get; set; }
    public ICollection<Item> Items { get; set; } = new List<Item>();
}

class Item 
{
    public long Id { get; set; }
    public long InvoiceId { get; set; }
    public Invoice Invoice { get; set; }
}

public class IcContext : DbContext
{
    public IcContext(DbContextOptions options) : base(options) { }
    public DbSet<Invoice> Invoices { get; set;    
    public DbSet<Item> Items { get; set; }
}

An Invoice can have many Items.

I want to get the list of Invoices from the DB using:

using (var db = new IcContext(_options))
{
    return db.Invoices.FromSql(RawSql).ToList();
}

What would be a valid RawSql string?

I have tried:

SELECT invoices.*, items.* 
FROM invoices 
LEFT JOIN items ON (invoices.id = items.invoiceId)

to no avail.

0 Answers