EF Core one-to-many with IQueryable

c# entity-framework-core linq

Question

I'm attempting to create a one-to-many relationship between a model based on a table (Driver) and a model based on a SQL query (DriverSchedule).

I'm having difficulty getting this architecture to function.

Driver model:

[Table("Drivers")]
public class Driver
{
    [Key]
    [Column("DriverKey")]
    public int ID { get; set; }
    ...
    public virtual ICollection<DriverSchedule> DriverSchedules { get; set; }
}

DriverSchedule model:

public class DriverSchedule
{
    public int DriverID { get; set; }  // foreign key
    public DateTime Date { get; set; }
    public bool IsScheduled { get; set; }
    public virtual Driver Driver { get; set; }
}

VendorDbContext context:

public class VendorDbContext : DbContext
{
    ...
    public DbSet<Driver> Drivers { get; set; }
    ...

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<DriverSchedule>().HasKey(table => new { table.DriverID, table.Date });
        modelBuilder.Entity<Driver>().HasMany(d => d.DriverSchedules).WithOne(s => s.Driver);
        ...
        modelBuilder.Query<DriverSchedule>();
    }

    public IQueryable<DriverSchedule> DriverSchedules(DateTime startingDate, DateTime endingDate) =>
        Query<DriverSchedule>().FromSql("<SQL>");
}

View:

@model IEnumerable<Driver>
...
@foreach (var item in Model)
{
    <tr>
        <td>@(item.Name)</td>
        <td>@(item.ID)</td>
        <td><ul>
            foreach (var driverSchedule in item.DriverSchedules)
            {
                <li>@(driverSchedule.ToString("MM/dd/yy") - @(driverSchedule.IsScheduled)</li>
            }
        </ul></td>
    </tr>
}
...

My LINQ query:

var model = await (
    from driver in _context.Drivers
    join schedule in _context.DriverSchedules(startDate.Date, endDate.Date) on driver.ID equals schedule.DriverID
    orderby driver.LastName, driver.FirstName
    select driver
).Include(x => x.DriverSchedules)
.ToListAsync();

When I run the code, this line:

modelBuilder.Query<DriverSchedule>();

generates an error:

The query type 'DriverSchedule' cannot be added to the model because an entity type with the same name already exists.

** edit 0 **

This posting https://msdn.microsoft.com/en-us/magazine/mt847184.aspx suggests that I need to

Remove the navigation property from Driver:

//public virtual ICollection<DriverSchedule> DriverSchedules { get; set; }

Remove the navigation property from DriverSchedule:

//public virtual Driver Driver { get; set; }

Remove the relationship definitions from OnModelCreating:

// modelBuilder.Entity<DriverSchedule>().HasKey(table => new { table.DriverID, table.Date });
// modelBuilder.Entity<Driver>().HasMany(d => d.DriverSchedules).WithOne(s => s.Driver);

Change the query interface:

IQueryable<DriverSchedule> DriverSchedules(DateTime startingDate, DateTime endingDate) =>
        Query<DriverSchedule>() ...

Define a relationship between the query and the entity:

modelBuilder.Query<DriverSchedule>().HasOne<Driver>().WithMany();

Then re-write the query:

model = await _context.DriverSchedules(startDate.Date, endDate.Date)
           .Include("Driver")
           .ToListAsync();

Which fails with this error:

The property 'Driver' is not a navigation property of entity type 'DriverSchedule'. The 'Include(string)' method can only be used with a '.' separated list of navigation property names.

** /edit 0 **

** edit 1 **

SELECT  m.DriverID, c.Date
        ,CAST( CASE WHEN COUNT(CASE WHEN c.date <> CAST(m.StartTime AS DATE) THEN NULL ELSE m.MovementID end)>0 THEN 1 ELSE 0 END AS BIT) IsScheduled
FROM    dbo.Calendar c
CROSS APPLY vMovements m
WHERE  1 = 1
AND   c.date BETWEEN {startingDate} AND {endingDate}
AND   m.DriverID IS NOT NULL
GROUP BY c.date, m.DriverID

** /edit 1 **

What am I missing?

1
2
7/1/2019 3:20:09 PM

Popular Answer

That's because you already have a entity called DriverSchedule

problem here:

modelBuilder.Entity<DriverSchedule>();
modelBuilder.Query<DriverSchedule>(); // wrong. 

create another model in order to get and map data results in DriverSchedules method. So:

public class Driversched
{
    public int DriverID { get; set; }  // foreign key
    public DateTime Date { get; set; }
    public bool IsScheduled { get; set; }
    public virtual Driver Driver { get; set; }
}

then:

modelBuilder.Query<Driversched>();

And finally:

 public IQueryable<Driversched> DriverSchedules(DateTime startingDate, DateTime endingDate) =>
        Query<DriverSchedule>().FromSql("<SQL>");

advice, Never use an entity class as a model.

1
6/28/2019 9:54:15 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow