如何配置Entity Framework Core，使用Fluent API映射从两个表中获取数据的实体？

huangapple
go评论88阅读模式
英文:

How to configure Entity Framework Core to map an entity that takes data from two tables with Fluent API?

问题

为了配置Entity Framework Core，我使用Fluent API。我想知道是否有一种方法可以映射一个实体，其中一些属性与一个表相关，而另一些属性与另一个表相关。

这仅用于查询目的，以便在一个实体中获得联接的结果。在我的情况下，我有以下实体：

  1. class Document
  2. {
  3.     long Id,
  4.     string Name,
  5.     long IdUserLastModification,
  6.     string UserNameLastModification,
  7.     DateTime DateLastModification,
  8. }

在我的数据库中，有两个表：

  • Document（IdDocument，Name，IdUserLastModification，DateLastModification）
  • User（IdUser，Name，Surname，UserName）

在T-SQL中，我会这样做：

  1. select 
  2.     IdDocument, Name, IdUser, Surname, DateLastModification 
  3. from 
  4.     Documents as d
  5. inner join 
  6.     User as u on u.IdUser = d.IdUserLastModification

谢谢！

英文:

To configure Entity Framework Core, I use the Fluent API. I would like to know if there is some way to map an entity that has some properties related with a table and another properties with another.

It is just for query purposes, to have in one entity the result of the join. In my case I have this entities:

  1. class Document
  2. {
  3.    long Id,
  4.    string Name,
  5.    long IdUserLastModification,
  6.    string UserNameLastModification,
  7.    DateTime DateLastModification,
  8. }

In my database I have two tables:

  • Document (IdDocument, Name, IdUserLastModification, DateLastModification)
  • User (IdUser, Name, Surname, UserName)

In T-SQL I would do in like this:

  1. select 
  2.     IdDocument, Name, IdUser, Surname, DateLastModification 
  3. from 
  4.     Documents as d
  5. inner join 
  6.     User as u on u.IdUser = d.IdUserLastModification

Thanks so much.

答案1

得分: 1

我不确定你的意思，但你可以在实体类中添加扩展方法和字段，根据用户ID返回经过筛选的文档列表（以下是未经测试的伪代码），并将用户名作为文档实体的一部分包括。

  1. public partial class Documents //假设这是DbSet
  2. {
  3.     public List<Document> GetDocumentsLastModified(long UserID)
  4.     {
  5.         var result = from d in dbContext.Documents
  6.                      join u in Users on d.IdUserLastModification equals u.IdUser
  7.                      where u.IdUser == UserID
  8.                      select new
  9.                      {
  10.                          UserName = u.UserName,
  11.                          Document = d
  12.                      };
  13.         return result.ToList();
  14.     }
  16.     [NotMapped] // 这会使EF Core生成的SQL忽略此字段，否则它将包括在所有文档查询中
  17.     public string UserName { get; set; } //如果包括了导航属性，可以在getter中执行一些逻辑，如其他人所解释的
  18. }

希望这有所帮助。

英文:

I'm not exactly sure what you mean, but you can add an extension method and field to your entity class to return the filtered document list based on a user id (untested pseudo code follows) and include the UserName as part of your document entity.

  1. public partial class Documents //Assuming this is the DbSet
  2. {
  3.     public List&lt;Document&gt; GetDocumentsLastModified(long UserID)
  4.     {
  5.          var result = from d in dbContext.Documents
  6.                  join User as u on d.IdUserLastModification equals u.IdUser
  7.                  select u.UserName, d.*
  8.                  where u.IdUser == UserID;
  9.          return result.ToList();
  10.      }
  11.      [NotMapped] // this keeps the sql generated by EF core to ignore this field otherwise it would include as part of all Document queries
  12.      public string UserName {get; set;} //you could do some of the logic in getter if you include the navigation as explained by others
  13. }

答案2

得分: 0

如果您有一个 dbcontext，可以使用类似这样的代码（LINQ Join）：

  1. var result = (from ep in dbContext.Documents
  2.               join u in dbContext.Documents on ep.IdUserLastModification equals u.IdUser
  3.               select new {
  4.                   UID = u.IdUser,
  5.                   Name = ep.Name,
  6.                   . . .
  7.               });

如果没有，您始终可以执行像您在问题中所做的那样的 SQL 查询。

英文:

If you have a dbcontext you can use something like this (LINQ Join):

  1. var result = (from ep in dbContext.Documents
  2.                      join u in dbContext.Documents on ep.IdUserLastModification equals u.IdUser
  3.                      select new {
  4.                          UID = u.IdUser,
  5.                          Name = ep.Name,
  6.                          . . .
  7.                      });

If you don't, you can always do an SQL query as you just did in the question.

答案3

得分: 0

以下是翻译好的部分：

  1. 你应该使用导航属性来设置你的类。
  3. public class Document
  4. {
  5.     public long Id { get; set; }
  6.     public string Name { get; set; }
  7.     public long IdUserLastModification { get; set; }
  8.     public string UserNameLastModification { get; set; }
  9.     public DateTime DateLastModification { get; set; }
  11.     public virtual User User { get; set; }
  12. }
  14. public class User
  15. {
  16.     public long IdUser { get; set; }
  17.     public string Name { get; set; }
  18.     public string Surname { get; set; }
  19.     public string UserName { get; set; }
  21.     public virtual ICollection<Document> Documents { get; } = new HashSet<Document>();
  22. }
  24. 然后在你的 `DbContext` 中使用 Fluent API 配置关系：
  26. modelBuilder.Entity<Document>(e =>
  27. {
  28.     e.HasKey(d => d.Id);
  30.     e.HasOne(d => d.User)
  31.         .WithMany(u => u.Documents)
  32.         .HasForeignKey(d => d.IdUserLastModification);
  33. });
  35. 然后，你可以使用匿名类型或 DTO 类来查询你想要的数据：
  37. var documents = await dbContext.Documents
  38.                 .Select(d => new
  39.                 {
  40.                     d.Id,
  41.                     d.Name,
  42.                     d.IdUserLastModification,
  43.                     d.User.Surname,
  44.                     d.DateLastModification
  45.                 })
  46.                 .ToListAsync();
英文:

You should setup your classes with Navigation properties.

  1. public class Document
  2. {
  3.     public long Id{ get; set; }
  4.     public string Name{ get; set; }
  5.     public long IdUserLastModification{ get; set; }
  6.     public string UserNameLastModification{ get; set; }
  7.     public DateTime DateLastModification{ get; set; }
  9.     public virtual User User { get; set; }
  10. }
  12. public class User
  13. {
  14.     public long IdUser { get; set; }
  15.     public string Name { get; set; }
  16.     public string Surname { get; set; }
  17.     public string UserName { get; set; }
  19.     public virtual ICollection&lt;Document&gt; Documents { get; } = new HashSet&lt;Document&gt;();
  20. }

Then configure the relationship in your DbContext using fluent API:

  1. modelBuilder.Entity&lt;Document&gt;(e =&gt;
  2. {
  3.     e.HasKey(d =&gt; d.Id);
  5.     e.HasOne(d =&gt; d.User)
  6.         .WithMany(u =&gt; u.Documents)
  7.         .HasForeignKey(d =&gt; d.IdUserLastModification);
  8. });

You can then query the data you want using either an anonymous type or a DTO class:

  1. var documents = await dbContext.Documents
  2.                 .Select(d =&gt; new
  3.                 {
  4.                     d.Id,
  5.                     d.Name,
  6.                     d.IdUserLastModification,
  7.                     d.User.Surname,
  8.                     d.DateLastModification
  9.                 })
  10.                 .ToListAsync();

huangapple
  • 本文由 发表于 2023年2月24日 00:09:12
  • 转载请务必保留本文链接：https://go.coder-hub.com/75547389.html
  • c#
  • entity-framework-core
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定