i have many to many relationship beetwen 6 tables Associative model:
public class Product
{
public int? GameId { get; set; }
public Game Game { get; set; } = new Game();
public int? PlatformId { get; set; }
public Platform Platform { get; set; } = new Platform();
public int? ManufactureId { get; set; }
public Manufacture Manufacture { get; set; } = new Manufacture();
public int? LocalizationId { get; set; }
public Localization Localization { get; set; } = new Localization();
public int? GenreId { get; set; }
public Genre Genre { get; set; } = new Genre();
public int? RegionrestrictId { get; set; }
public Country RegionRestrict { get; set; } = new Country();
}
main related model:
public class Game
{
public int Id { get; }
public string Titleofgame { get; set; }
public string Description { get; set; }
public DateTime DateRelese { get; set; }
public List<Manufacture> Manufactures { get; set; } = new List<Manufacture>();
public List<Product> Products { get; set; } = new List<Product>();
public int? Idseriesofgame { get; set; }
public Serie Series { get; set; }
public int? Idpublication { get; set; }
public Publication Publication { get; set; }
public List<Platform> Platforms { get; set; } = new List<Platform>();
public List<Localization> Localizations { get; set; } = new List<Localization>();
public List<Genre> Genres { get; set; } = new List<Genre>();
public List<Country> RegionRestricts { get; set; } = new List<Country>();
}
and one of the tables wicht related with "Game":
public class Genre
{
public int Id { get; }
[Display(Name ="Жанр")]
public string Titleofgenre { get; set; }
public List<Product> Products { get; set; } = new List<Product>();
public List<Game> Games { get; set; } = new List<Game>();
}
(other tables are the same)and there is a table in Mysql "Products":
and also some data:
when i try to get data from code:
IEnumerable<Product> query = _context.Products.Include(d => d.Game).ThenInclude(d=>d.Genres).Include(d=>d.Game.Manufactures).Include(d=>d.Game.Platforms).Include(d=>d.Game.Localizations).Include(d=>d.Game.RegionRestricts).Include(d => d.Genre).Include(d => d.Localization).Include(d => d.Platform).Include(d => d.RegionRestrict).Include(d => d.Manufacture).ToList();
Entity framework generate such a query:
SELECT `p`.`gameid`, `p`.`genreid`, `p`.`localizationid`, `p`.`manufactureid`, `p`.`platformid`, `p`.`regionrestrictid`, `g`.`id`, `g`.`daterelese`, `g`.`description`, `g`.`idpublication`, `g`.`idseriesofgame`, `g`.`titleofgame`, `g0`.`id`, `g0`.`titleofgenre`, `l`.`id`, `l`.`titleoflocalization`, `p0`.`id`, `p0`.`titleofplatform`, `c`.`id`, `c`.`titleofcountry`, `m`.`id`, `m`.`titleofmanufactures`
FROM `Products` AS `p`
INNER JOIN `games` AS `g` ON `p`.`gameid` = `g`.`id`
INNER JOIN `genres` AS `g0` ON `p`.`genreid` = `g0`.`id`
INNER JOIN `localizations` AS `l` ON `p`.`localizationid` = `l`.`id`
INNER JOIN `platforms` AS `p0` ON `p`.`platformid` = `p0`.`id`
INNER JOIN `countries` AS `c` ON `p`.`regionrestrictid` = `c`.`id`
INNER JOIN `manufactures` AS `m` ON `p`.`manufactureid` = `m`.`id`
therefore i'm getting only 4 of 11 elements:
also there is configuration in fluent api:
modelBuilder
.Entity<Game>()
.HasMany(c => c.Localizations)
.WithMany(s => s.Games)
.UsingEntity<Product>(
j =>
j.HasOne(pt => pt.Localization)
.WithMany(t => t.Products)
.HasForeignKey(pt => pt.LocalizationId),
j =>
j.HasOne(pt => pt.Game)
.WithMany(p => p.Products)
.HasForeignKey(pt => pt.GameId),
j =>
{
j.HasKey(t => new { t.GameId, t.ManufactureId, t.GenreId, t.PlatformId, t.RegionrestrictId });
j.ToTable("Products");
}
);
modelBuilder
.Entity<Game>()
.HasMany(c => c.Platforms)
.WithMany(s => s.Games)
.UsingEntity<Product>(
j =>
j.HasOne(pt => pt.Platform)
.WithMany(t => t.Products)
.HasForeignKey(pt => pt.PlatformId),
j =>
j.HasOne(pt => pt.Game)
.WithMany(p => p.Products)
.HasForeignKey(pt => pt.GameId),
j =>
{
j.HasKey(t => new { t.GameId, t.ManufactureId, t.GenreId, t.PlatformId, t.RegionrestrictId });
j.ToTable("Products");
}
);
modelBuilder
.Entity<Game>()
.HasMany(c => c.Genres)
.WithMany(s => s.Games)
.UsingEntity<Product>(
j =>
j.HasOne(pt => pt.Genre)
.WithMany(t => t.Products)
.HasForeignKey(pt => pt.GenreId),
j =>
j.HasOne(pt => pt.Game)
.WithMany(p => p.Products)
.HasForeignKey(pt => pt.GameId),
j =>
{
j.HasKey(t => new { t.GameId, t.ManufactureId, t.GenreId, t.PlatformId, t.RegionrestrictId });
j.ToTable("Products");
}
);
modelBuilder
.Entity<Game>()
.HasMany(c => c.RegionRestricts)
.WithMany(s => s.Games)
.UsingEntity<Product>(
j =>
j.HasOne(pt => pt.RegionRestrict)
.WithMany(t => t.Products)
.HasForeignKey(pt => pt.RegionrestrictId),
j =>
j.HasOne(pt => pt.Game)
.WithMany(p => p.Products)
.HasForeignKey(pt => pt.GameId),
j =>
{
j.HasKey(t => new { t.GameId, t.ManufactureId, t.GenreId, t.PlatformId, t.RegionrestrictId });
j.ToTable("Products");
}
);
modelBuilder.Entity<Game>()
.HasOne(a => a.Series)
.WithMany(a => a.Games)
.HasForeignKey(c => c.Idseriesofgame);
modelBuilder
.Entity<Game>()
.HasMany(c => c.Manufactures)
.WithMany(s => s.Games)
.UsingEntity<Product>(
j =>
j.HasOne(pt => pt.Manufacture)
.WithMany(t => t.Products)
.HasForeignKey(pt => pt.ManufactureId),
j =>
j.HasOne(pt => pt.Game)
.WithMany(p => p.Products)
.HasForeignKey(pt => pt.GameId).OnDelete(DeleteBehavior.Cascade),
j =>
{
j.HasKey(t => new { t.GameId, t.ManufactureId, t.GenreId, t.PlatformId, t.RegionrestrictId });
j.ToTable("Products");
}
);
modelBuilder.Entity<Game>().HasMany(d => d.Products).WithOne(d => d.Game).HasForeignKey(d => d.GameId).OnDelete(DeleteBehavior.Cascade);
modelBuilder.Entity<Product>()
.HasOne(bc => bc.Game)
.WithMany(b => b.Products)
.HasForeignKey(bc => bc.GameId);
modelBuilder.Entity<Product>()
.HasOne(bc => bc.RegionRestrict)
.WithMany(b => b.Products)
.HasForeignKey(bc => bc.RegionrestrictId);
modelBuilder.Entity<Product>()
.HasOne(bc => bc.Genre)
.WithMany(b => b.Products)
.HasForeignKey(bc => bc.GenreId);
modelBuilder.Entity<Product>()
.HasOne(bc => bc.Localization)
.WithMany(b => b.Products)
.HasForeignKey(bc => bc.LocalizationId);
modelBuilder.Entity<Product>()
.HasOne(bc => bc.Manufacture)
.WithMany(b => b.Products)
.HasForeignKey(bc => bc.ManufactureId);
modelBuilder.Entity<Product>()
.HasOne(bc => bc.Platform)
.WithMany(b => b.Products)
.HasForeignKey(bc => bc.PlatformId);
modelBuilder.Entity<Game>().HasKey(p => p.Id);
modelBuilder.Entity<Genre>().HasKey(p => p.Id);
modelBuilder.Entity<Localization>().HasKey(p => p.Id);
modelBuilder.Entity<Manufacture>().HasKey(p => p.Id);
modelBuilder.Entity<Platform>().HasKey(p => p.Id);
modelBuilder.Entity<Product>()
.HasKey(bc => bc.GameId );
its suppose to be left join if i want to get all data, but i dont know how to do it.
