C#•2y ago

How do I do an inner GroupJoin on dotnet?

I'm using .NET 6 with Entity Framework. This is my situation: I have a BenefitEntity (with Name and Description properties and a relationship with BenefitCategory) and this BenefitCategory which has Name and Description as well.

In the application, all the Names and Descriptions are translated, we have a TranslationEntities table that contains all the translations.

Here are the three classes (I'll keep it simple and put just the properties we need):

C#
public class BenefitEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public Guid BenefitCategoryId { get; set; }
    public virtual BenefitCategoryEntity CategoryEntity { get; set; }
}

public class BenefitCategoryEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
}

public class TranslationEntity
{
    public Guid EntityId { get; set; }
    public string Language { get; set; }
    public string Property { get; set; }
    public string Value { get; set; }
}

C#
public class BenefitEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public Guid BenefitCategoryId { get; set; }
    public virtual BenefitCategoryEntity CategoryEntity { get; set; }
}

public class BenefitCategoryEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
}

public class TranslationEntity
{
    public Guid EntityId { get; set; }
    public string Language { get; set; }
    public string Property { get; set; }
    public string Value { get; set; }
}

C#
public class BenefitEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public Guid BenefitCategoryId { get; set; }
    public virtual BenefitCategoryEntity CategoryEntity { get; set; }
}

public class BenefitCategoryEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
}

public class TranslationEntity
{
    public Guid EntityId { get; set; }
    public string Language { get; set; }
    public string Property { get; set; }
    public string Value { get; set; }
}

C#
public class BenefitEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public Guid BenefitCategoryId { get; set; }
    public virtual BenefitCategoryEntity CategoryEntity { get; set; }
}

public class BenefitCategoryEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
}

public class TranslationEntity
{
    public Guid EntityId { get; set; }
    public string Language { get; set; }
    public string Property { get; set; }
    public string Value { get; set; }
}

The TranslationEntities table contains all the translations, for all the entities type. I want to get all the BenefitEntity, using the GroupJoin for their Name/Description and do another inner join for its BenefitCategory name and description. I'm able to do the first one, but not able to do the second one.

RasoOP•5/9/24, 8:45 AM

Here is what I did:

C#
    public async Task<ICollection<BenefitEntity>> GetAllBenefitEntitiesAsync()
    {
        var benefitsQuery = Entities
            .Include(a => a.CategoryEntity);

        var translationsQuery = DbContext.TranslationsEntities!
            .AsNoTracking()
            .Where(e => e.Language.Equals(LANGUAGE));

        var benefits = await benefitsQuery.ToListAsync();

        var result = benefits
            .GroupJoin(
                translationsQuery,
                benefit => benefit.Id,
                translation => translation.EntityId,
                (benefit, translation) => new { Benefit = benefit, Translation = translation })
            .Select(result => new BenefitEntity
            {
                    // Benefit Name and Description from joined translations
                    Name = result.Translation.FirstOrDefault(e => e.Property.Equals(nameof(BenefitEntity.Name)))?.Value ?? result.Benefit.Name,
                    Description = result.Translation.FirstOrDefault(e => e.Property.Equals(nameof(BenefitEntity.Description)))?.Value ?? result.Benefit.Description,
                    Id = result.Benefit.Id,
                    CategoryEntity = result.Benefit.CategoryEntity,
                    BenefitCategoryId = result.Benefit.BenefitCategoryId,
            })
            .AsQueryable();

        return result.ToList();
}

C#
    public async Task<ICollection<BenefitEntity>> GetAllBenefitEntitiesAsync()
    {
        var benefitsQuery = Entities
            .Include(a => a.CategoryEntity);

        var translationsQuery = DbContext.TranslationsEntities!
            .AsNoTracking()
            .Where(e => e.Language.Equals(LANGUAGE));

        var benefits = await benefitsQuery.ToListAsync();

        var result = benefits
            .GroupJoin(
                translationsQuery,
                benefit => benefit.Id,
                translation => translation.EntityId,
                (benefit, translation) => new { Benefit = benefit, Translation = translation })
            .Select(result => new BenefitEntity
            {
                    // Benefit Name and Description from joined translations
                    Name = result.Translation.FirstOrDefault(e => e.Property.Equals(nameof(BenefitEntity.Name)))?.Value ?? result.Benefit.Name,
                    Description = result.Translation.FirstOrDefault(e => e.Property.Equals(nameof(BenefitEntity.Description)))?.Value ?? result.Benefit.Description,
                    Id = result.Benefit.Id,
                    CategoryEntity = result.Benefit.CategoryEntity,
                    BenefitCategoryId = result.Benefit.BenefitCategoryId,
            })
            .AsQueryable();

        return result.ToList();
}

Starting from that point, how can I do another Join for CategoryEntity, using the same translationQuery, joining the categoryEntity.Id with translation.EntityId ?

C# public async Task<ICollection<BenefitEntity>> GetAllBenefitEntitiesAsync() { var benefitsQuery = Entities .Include(a => a.CategoryEntity); var translationsQuery = DbContext.TranslationsEntities! .AsNoTracking() .Where(e => e.Language.Equals(LANGUAGE)); var benefits = await benefitsQuery.ToListAsync(); var result = benefits .GroupJoin( translationsQuery, benefit => benefit.Id, translation => translation.EntityId, (benefit, translation) => new { Benefit = benefit, Translation = translation }) .Select(result => new BenefitEntity { // Benefit Name and Description from joined translations Name = result.Translation.FirstOrDefault(e => e.Property.Equals(nameof(BenefitEntity.Name)))?.Value ?? result.Benefit.Name, Description = result.Translation.FirstOrDefault(e => e.Property.Equals(nameof(BenefitEntity.Description)))?.Value ?? result.Benefit.Description, Id = result.Benefit.Id, CategoryEntity = result.Benefit.CategoryEntity, BenefitCategoryId = result.Benefit.BenefitCategoryId, }) .AsQueryable(); return result.ToList(); }

How do I do an inner GroupJoin on dotnet?

Similar Threads

Similar Threads

Similar Threads