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.