Entityframework - Contains Query of Composite Key

c# linq filter group by max composite key

Here is a query that works for me:

await dbContex.ShoppingListPatientReports
               .Where(report => patientIds.Contains(report.PatientId))
               .GroupBy(report => report.PatientId)
               .Select(patientShoppingList =>
                        new
                          {
                            patientId = patientShoppingList.Key,
                                             //Grouping reports by created date and take group wiht the most recent date  
                             reports = patientShoppingList.GroupBy(report => DbFunctions.TruncateTime(report.DateCreated))
                                                                          .OrderByDescending(group => group.Key)
                                                                          .FirstOrDefault()
                          })
   .SelectMany(g => g.reports.Select(r => r))
   .ToListAsync();

Is it possible to select huge list from db by list of composite primary keys

Ok, it was much easier than i thought. Both databases are in the same SQL server, so it was mater of simple inner join.

I just added properties from Item to OtherDbItem

public class OtherDbItem
{
    public string A { get; set; }
    public string B { get; set; }
    public string C { get; set; }
    public DateTime Date { get; set; }
    public string X { get; set; }
    public string Y { get; set; }
    public string Z { get; set; }
    public string FewOtherProperties { get; set; }
}

And in OnModemCreating:

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<OtherDbItem>(
            entity =>
            {
                entity.ToSqlQuery(@"SELECT
i.A,
i.B,
i.C,
i.Date,
o.X,
o.Y,
o.Z,
o.FewOtherProperties
  FROM [DB1].[dbo].[Items] i
  inner join [DB2].[dbo].[OtherDbItem] o on i.A = o.X and i.B = o.Y and i.C = o.Z");
                entity.HasKey(o => new { o.X, o.Y, o.Z});
            });
    }

And last thing to do:

    {
        return this.context.OtherDbItems
            .Where(x => x.Date == date)
            .Distinct()
            .ToListAsync();
    }

EntityFramework Core relation to composite key

You won't be able to map this correctly in EF. If you want House to refer to Area, the foreign key should consist of the same fields as Area's composite key, otherwise EF won't accept the mapping. A work-around could be to skip the mapping and to join the entities manually when necessary, but that conceals the real issue: poor design.

The major design flaw is that you have to duplicate an Area when translations are added. Now the question is -- and always will be -- Which record represents my physical Area entity? The basic premise of a relational database is that entities are represented by unique records. Your design violates that core principle.

Unfortunately the tables cannot be altered in any way.

Well, they should be! Leaving it this way shouldn't even be considered. You shouldn't work with a warped relational model, it's too pivotal for smooth application development.

The model, as I can piece it together from your description, should probably be something like this:

public class House
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? AreaId { get; set; }
    public Area Area { get; set; }
}

public class Area
{
    public int Id { get; set; }
    public int CountryId { get; set; }
    public Country Country { get; set; }
    public string Name { get; set; } // E.g. the name in a default language
    public ICollection<AreaTranslation> AreaTranslations { get; set; }
}

public class AreaTranslation
{
    public int AreaId { get; set; }
    public int LanguageId { get; set; }
    public string LocalizedName { get; set; }
}

public class Country
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Language
{
    public int Id { get; set; }
    public string Name { get; set; }
}

For this model you need one explicit mapping instruction (EF will infer the rest):

modelBuilder.Entity<AreaTranslation>()
            .HasKey(a => new { a.AreaId, a.LanguageId });

You see that Area now genuinely represents a physical area out there. A House now naturally has one Area, not this weird collection of Areas that must be considered as one area somehow. The various languages come into play by the AreaTranslation junction class. I assume that an Area belongs to one Country.

---

Related Topics