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?
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.