What the best way of translating created user name with ef core 3.1

Question

public class Entity
{
    public string Id { get; set; }
    public string CreatedBy { get; set; }
    public string LastUpdatedBy { get; set; }
    [NotMapped]
    public string Creator { get; set; }
    [NotMapped]
    public string Updater { get; set; }
}

public class User
{
    public string Name { get; set; }
    public string Id { get; set; }
}

I am going to search Entities, sort on Creator/Updater properties(and return UserInfo.Name) with ef core query, any idea?

Lei Chi · Accepted Answer · 2021-06-15T07:07:42.047

After hours of researches, refers

How do you perform a left outer join using linq extension methods

Entity Framework Join 3 Tables

There 3 ways in oder(presonal perfer the first than second):

class Program
{
    static void Main()
    {
        using TenantDBContext dbContext = new TenantDBContext("PORT=5432;DATABASE=linqtosql;HOST=xxx.com;PASSWORD=xxx;USER ID=postgres;Pooling=true;Minimum Pool Size=10;Application Name=xxx");

        var result = (
            from entity in dbContext.Entities
            join user in dbContext.Users on entity.CreatedBy equals user.Id into temp1
            from ce in temp1.DefaultIfEmpty()
            join user1 in dbContext.Users on entity.UpdatedBy equals user1.Id into temp2
            from cu in temp2.DefaultIfEmpty()
            select new Entity() { Id = entity.Id, CreatedBy = entity.CreatedBy, UpdatedBy = entity.UpdatedBy, Creator = ce.Name, Updater = ce.Name }
            ).ToList();

        Console.WriteLine(JsonConvert.SerializeObject(result, Formatting.Indented));

        var result2 = dbContext.Entities
            .GroupJoin(dbContext.Users, e => e.CreatedBy, u => u.Id, (e, u) => new { Entity = e, User = u })
            .SelectMany(eUser => eUser.User.DefaultIfEmpty(), (e, u) => new Entity() { Id = e.Entity.Id, CreatedBy = e.Entity.CreatedBy, UpdatedBy = e.Entity.UpdatedBy, Creator = u.Name })
            .GroupJoin(dbContext.Users, e => e.UpdatedBy, u => u.Id, (e, u) => new { Entity = e, User = u })
            .SelectMany(eUser => eUser.User.DefaultIfEmpty(), (e, u) => new Entity() { Id = e.Entity.Id, CreatedBy = e.Entity.CreatedBy, UpdatedBy = e.Entity.UpdatedBy, Creator = e.Entity.Creator, Updater = u.Name }
            ).ToList();

        Console.WriteLine(JsonConvert.SerializeObject(result2, Formatting.Indented));

        var result3 = dbContext.Entities
            .SelectMany(entity => dbContext.Users.Where(user => entity.CreatedBy == user.Id).DefaultIfEmpty(), (entity, user) => new { Entity = entity, User = user })
            .SelectMany(entity => dbContext.Users.Where(user => entity.Entity.UpdatedBy == user.Id).DefaultIfEmpty(), (entity, user) => new Entity { Id = entity.Entity.Id, CreatedBy = entity.Entity.CreatedBy, UpdatedBy = entity.Entity.UpdatedBy, Creator = entity.User.Name, Updater = user.Name })
            .ToList();

        Console.WriteLine(JsonConvert.SerializeObject(result2, Formatting.Indented));
    }
}

*"After hours of researches..."* That's because you are not using the main benefits of EF - navigation properties. Create proper model - no `[NotMapped]` things, navigation properties instead of/additional to FKs, e.g. `public User CreatedBy { get; set; }`. Then all the queries will be simple, e.g. `dbContext.Entities.Select(e => new { Id = e.Id, Creator = e.CreatedBy.Name, ...})` — Ivan Stoev, Jun 15 '21 at 08:03

What the best way of translating created user name with ef core 3.1

1 Answers1