CODEWITHSUNDEEP
c#entity-framework
user2915962
user2915962

Reputation: 2711

Entity framework relationships

I have these three entities:

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´ve been getting help from here before in order to set it up like this. Entity Framework errors when trying to create many-to-many relationship

So the way it is now I guess the result is the "glue" that ties these classes together containing foreign keys to the two other tables.

What I have been trying to achieve for days now is to:

  1. Create an event.
  2. Add dogs to the event.
  3. Add results to the dogs participating in the choosenEvent.

Lets say I create an event like this:

[HttpPost]
public ActionResult CreateEvent(Event newEvent)
{
    newEvent.EventDate = newEvent.EventDate.ToString();
    _ef.AddEvent(newEvent);

    return View();
}

Now I guess the next step would be to add a list of dogs to this event and in order to do that I need to somehow use my result-class since that's the "glue"-class. Please let me know if I'm even on the right track here.

Upvotes: 0

Views: 139

Answers (2)

Callum Linington
Callum Linington

Reputation: 14417

It is not really a good idea to do many to many relationships like how you've done. See here

In order to get a proper many to many relationship, mapped in the proper way in the database, that doesn't have pitfalls, I would try it this way:

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 now write your query 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 is a bit nasty looking, but it will give you back a list of anonymous objects containing a Dog and its related Result. But obviously it would be better to do this in a stored proc:

using (var context = new DbContext())
{
    var results = context.Database.ExecuteStoreQuery<SomeResultDto>("SELECT * .... JOIN ... ");
}

This is cleaner, because you are using SQL.

This is a more complex way of dealing with it. But far more performant, especially if you understand fully how entity framework executes 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 completely up to you how you create these links. To turn this into a sproc as well, you want to create some custom User Defined Table Types and use them as a Table Value Parameter.

var dogResults = dogs.SelectMany( d => results.Select ( r => new DogResult { DogId = d.Id, ResultId = r.Id } ) );

That is a beast of a LINQ query and basically it gets every dog and links it to every result. Run it in LinqPad and Dump the values.

Upvotes: 1

cjb110
cjb110

Reputation: 1471

I've only done this using the fluent method (when I was learning I found you can do everything in fluent, but not with annotations, so I've not looked into them), the following creates a many to many between my Unit entity and my UnitService entity:

modelBuilder.Entity<Unit>()
            .HasMany<UnitService>(u => u.Services)
            .WithMany(us => us.Units);

This code is in the protected override void OnModelCreating(DbModelBuilder modelBuilder) method.

In your case Event is Unit and Dog is UnitService.

Oh ooops, you don't need that at all, your 'join' table is your results table, in my case I don't care about the join table so its all hidden. Maybe something like:

   modelBuilder.Entity<Result>()
               .HasMany<Event>(e => e.Results);
   modelBuilder.Entity<Result>()
               .HasMany<Dog>(d => d.Results);

Upvotes: 1

Related Questions