Tomino Tomino - 1 month ago 13
C# Question

Unable to query over many-to-many relationship

does anybody know how to query DB in EF Core for many-to-many relationship, but more like left outer join from one side?

Let me explain what I mean.

Currency.cs

public class Currency
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid UID { get; set; } = Guid.NewGuid();

public string ISOCode { get; set; }

public string Symbol { get; set; }

[JsonIgnore]
public List<RegionCurrency> RegionCurrencies { get; set; }
}


RegionCurrency.cs

public class RegionCurrency
{
public Guid CurrencyUID { get; set; }

public Guid RegionUID { get; set; }

[ForeignKey("CurrencyUID")]
public Currency Currency { get; set; }

[ForeignKey("RegionUID")]
public Region Region { get; set; }
}


Region.cs

public class Region
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid UID { get; set; } = Guid.NewGuid();

[StringLength(8)]
public string CountryISOCode { get; set; }

public List<RegionCurrency> RegionCurrencies { get; set; }
}


MyContext.cs

public class LookupTablesContext : DbContext
{
public virtual DbSet<Currency> Currecies { get; set; }

public virtual DbSet<RegionCurrency> RegionCurrency { get; set; }

public virtual DbSet<Region> Regions { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.HasDefaultSchema(SchemaName);

modelBuilder.Entity<RegionCurrency>()
.HasKey(t => new { t.CurrencyUID, t.RegionUID })
.HasName("PK_RegionCurrency");

modelBuilder.Entity<RegionCurrency>()
.HasOne(pt => pt.Region)
.WithMany(p => p.RegionCurrencies)
.HasForeignKey(pt => pt.RegionUID);

modelBuilder.Entity<RegionCurrency>()
.HasOne(pt => pt.Currency)
.WithMany(p => p.RegionCurrencies)
.HasForeignKey(pt => pt.CurrencyUID);

modelBuilder.Entity<Currency>()
.HasIndex(c => c.ISOCode)
.HasName("UX_Currency_ISOCode")
.IsUnique();

modelBuilder.Entity<Region>()
.HasIndex(c => c.CountryISOCode)
.HasName("UX_Region_CountryISOCode")
.IsUnique();
}
}


My query:

var result = ctx.Currencies
.Include(c => c.RegionCurrencies)
.ThenInclude(rc => rc.Select(rcs => rcs.Regions)) // This seems to be wrong
.SingleOrDefault(c => c.ISOCode == "EUR");


I also tried to use includes as you can see below on the picture:

many-to-many relationship include

Please note, that RegionCurrencies table can contain 0-N relations and I want to get Currency entity even there's no record in RegionCurrency table.

This (and similar tries) ended up in exception like this:

An exception of type 'System.ArgumentException' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code

Additional information: The property expression 'rc => {from RegionCurrency rc in rcs select [pts].Regions}' is not valid. The expression should represent a property access: 't => t.MyProperty'. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.

Dependencies:
"Microsoft.EntityFrameworkCore": "1.0.1",
"Microsoft.EntityFrameworkCore.SqlServer": "1.0.1",


I cannot find any working example. But certainly I'm just blind.

Thanks for any help.

Answer

You can do it as shown below.

var tag = ctx.Tags.Include(t => t.PostTags)
             .ThenInclude(p => p.Post).FirstOrDefault(d => d.TagId == "2");

var posts = tag.PostTags.Select(c => c.Post).ToList();

Note : Sometimes VS doesn't show intelisence properly.So beware of intellisense :D .One solution may be for that is,close the VS and restart a new instance of it.

Result :

value of tag :

enter image description here

values of posts :

enter image description here

Test data :

enter image description here

enter image description here

enter image description here

Update :

It's working.Please see the code.

var currency = db.Currecies.Include(t => t.RegionCurrencies)
                           .ThenInclude(p => p.Region)   
                           .FirstOrDefault(t => t.UID == Guid.Parse("0f8fad5b-d9cb-469f-a165-70867728950e"));

var regions = currency.RegionCurrencies.Select(c => c.Region).ToList();

Result :

value of currency :

enter image description here

values of regions :

enter image description here

Git Repo : EfCoreManyToMany

Comments