Structural relationships of entities
I have three objects:
public class Dog
{
public int DogId { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public bool Checked { get; set; }
public string DogImage { get; set; }
public virtual ICollection<Result> Results { get; set; }
}
public class Event
{
public int EventId { get; set; }
public string EventName { get; set; }
public string EventLocation { get; set; }
public string EventType { get; set; }
public string EventDate { get; set; }
public virtual ICollection<Result> Results { get; set; }
}
public class Result
{
public int ResultId { get; set; }
public int Track { get; set; }
public int Obedience { get; set; }
public int Protection { get; set; }
[ForeignKey("Dog")]
public int DogId { get; set; }
public virtual Dog Dog { get; set; }
[ForeignKey("Event")]
public int EventId { get; set; }
public virtual Event Event { get; set; }
}
I got help here before to set it up like this. Entity Framework Errors When Trying to Create Many-to-Many Relationships
So now I suppose result
it's the "glue" that binds these classes together with the foreign keys for the other two tables.
What I have been trying to achieve for a few days is this:
- Create an event.
- Add dogs to the event.
- Add results for dogs participating in the choosenEvent.
Suppose I create an event like this:
[HttpPost]
public ActionResult CreateEvent(Event newEvent)
{
newEvent.EventDate = newEvent.EventDate.ToString();
_ef.AddEvent(newEvent);
return View();
}
Now I suppose the next step is to add a list of dogs to this event, and for that I need to somehow use my result class, since the class is "glue". Please let me know if I am even on the right track here.
source to share
It's actually not a good idea to do many for many relationships, like the way you did it. See here
To get the correct many-to-many relationship mapped appropriately in the database, which has no pitfalls, I would try it like this:
public class Dog {}
public class Event {}
public class Result {}
// This is a linking table between Dog and Results
public class DogResult
{
public int Id {get;set;}
public int DogId {get;set;}
public int ResultId {get;set;}
}
// This is a linking table between Events and Results
public class EventResult
{
public int Id {get;set;}
public int EventId {get;set;}
public int ResultId {get;set;}
}
When you write your query now, you can do this:
using (var context = new DbContext())
{
var dogs = context.Dogs();
var dogResults = context.DogResults();
var results = context.Results();
var dogsAndResults = dogs.Join(
dogResults,
d => d.Id,
r => r.DogId,
(dog, dogResult) => new { dog, dogResult })
.Join(
results,
a => a.dogResult.ResultId,
r => r.Id,
(anon, result) => new { anon.dog, result });
}
It looks a bit unpleasant, but it will return you a list of anonymous objects containing Dog
and associated with it Result
. But obviously it would be better to do it in the saved proc:
using (var context = new DbContext())
{
var results = context.Database.ExecuteStoreQuery<SomeResultDto>("SELECT * .... JOIN ... ");
}
It's cleaner because you are using SQL.
This is a more difficult way to deal with it. But much more efficient, especially if you fully understand how the entity framework does LINQ.
Obviously if you want to create these links:
using (var context = new DbContext())
{
context.Dogs.AddRange(dogs); // dogs being a list of dog entities
context.Results.AddRange(results); // events being a list of results entities
context.DogResults.AddRange(dogResults); // a list of the links
}
It is entirely up to you how you create these links. To turn this into a sproc, you want to create some custom user defined datatypes and use them as a table value parameter.
var dogResults = dogs.SelectMany( d => results.Select ( r => new DogResult { DogId = d.Id, ResultId = r.Id } ) );
This is the beast of a LINQ query, and basically it gets every dog and binds it to every result. Run it in LinqPad and Dump
values.
source to share
I only did this using the free method (when I was learning I found that you can do everything freely, but not with annotations, so I did not study them), the following creates a set for many between my Unit
and my object UnitService
:
modelBuilder.Entity<Unit>()
.HasMany<UnitService>(u => u.Services)
.WithMany(us => us.Units);
This code is in the method protected override void OnModelCreating(DbModelBuilder modelBuilder)
.
In your case, Event
there is Unit
and Dog
is UnitService
.
Oh, oooh, you don't need that at all, your "join" table is a result table, in my case I don't need a join table so everything is hidden. Maybe something like:
modelBuilder.Entity<Result>()
.HasMany<Event>(e => e.Results);
modelBuilder.Entity<Result>()
.HasMany<Dog>(d => d.Results);
source to share