实体框架关系 [英] Entity framework relationships

问题描述

我有以下三个实体：

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.

我几天来一直在努力实现的目标是：

1. 创建活动。


2. 将狗添加到活动中。


3. 将结果添加到参加ChoosenEvent的狗中。 / li>
   

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.

推荐答案

做很多事情并不是一个好主意许多关系，例如您的工作方式。 请参阅此处

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 });
}

这看起来有点讨厌，但是会给您一个列表包含 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 ... ");
}

这比较干净，因为您使用的是SQL。

This is cleaner, because you are using SQL.

这是一种更复杂的处理方式。但是性能要高得多，尤其是在您完全了解实体框架如何执行LINQ的情况下。

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 } ) );

这是LINQ查询的野兽，基本上它会获取所有狗并将其链接到每个结果。在LinqPad中运行它，并 Dump 值。

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.

这篇关于实体框架关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！