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

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:

class Document
{
   long Id,
   string Name,
   long IdUserLastModification,
   string UserNameLastModification,
   DateTime DateLastModification,
}

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:

select 
    IdDocument, Name, IdUser, Surname, DateLastModification 
from 
    Documents as d
inner join 
    User as u on u.IdUser = d.IdUserLastModification

Thanks so much.

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.

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

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

var result = (from ep in dbContext.Documents
                     join u in dbContext.Documents on ep.IdUserLastModification equals u.IdUser
                     select new {
                         UID = u.IdUser,
                         Name = ep.Name,
                         . . .
                     });

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

You should setup your classes with Navigation properties.

public class Document
{
    public long Id{ get; set; }
    public string Name{ get; set; }
    public long IdUserLastModification{ get; set; }
    public string UserNameLastModification{ get; set; }
    public DateTime DateLastModification{ get; set; }

    public virtual User User { get; set; }
}

public class User
{
    public long IdUser { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public string UserName { get; set; }

    public virtual ICollection<Document> Documents { get; } = new HashSet<Document>();
}

Then configure the relationship in your DbContext using fluent API:

modelBuilder.Entity<Document>(e =>
{
    e.HasKey(d => d.Id);

    e.HasOne(d => d.User)
        .WithMany(u => u.Documents)
        .HasForeignKey(d => d.IdUserLastModification);
});

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

var documents = await dbContext.Documents
                .Select(d => new
                {
                    d.Id,
                    d.Name,
                    d.IdUserLastModification,
                    d.User.Surname,
                    d.DateLastModification
                })
                .ToListAsync();