There is an EF6 Code-First. There are also File and Error entities:

public partial class MyContext : DbContext { // ... public virtual DbSet<Error> Errors { get; set; } public virtual DbSet<File> Files { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { // ... modelBuilder.Entity<Error>() .HasMany(e => e.Files) .WithMany(e => e.Errors) .Map(m => m.ToTable("FileError").MapLeftKey("ErrorId").MapRightKey("FileId")); // ... } // ... } public partial class File { public File() { Errors = new HashSet<Error>(); } public int Id { get; set; } // ... public virtual ICollection<Error> Errors { get; set; } } public partial class Error { public Error() { Files = new HashSet<File>(); } public int Id { get; set; } // ... public virtual ICollection<File> Files { get; set; } }

As a result, 3 tables are created in the database: Files, Errors and FileError staging table. To export to Excel, I need to have an instance of a List or DataTable. It would be nice to use Join through an intermediate table, but there is no entity for it. My problem is not understanding how to create a request to select all files with errors:

 // Возвращает сущности Error. // Для доступа к свойствам File нужно перебирать // свойство Files в каждой записи. var q = from e in Errors where e.Files.Count > 0 select e; // а хотелось бы... var q = from e in Errors join f in Files in f.Errors equals e.Files select new { FileId = f.Id, ErrorId = e.Id, FilePath = f.FullPath, ErrorDescription = e.Description };
  • why do not you want to use the navigation properties ? get the required list, get the error / errors through the navigation property - Bald

2 answers 2

You can write as many statements from as you need - there are no restrictions on this:

 var q = from e in db.Errors from f in e.Files select new { FileId = f.Id, ErrorId = e.Id }

This nesting when compiling will be replaced by the use of the SelectMany method. You can also call it yourself if you like this option more:

 var q = db.Errors.SelectMany(e => e.Files.Select(f => new { FileId = f.Id, ErrorId = e.Id } ) );

It should be understood that this option will lead to an internal connection. That is, if the Error entity has no associated files, it will not fall into the final sample. If you need an analogue of the left external connection (LEFT JOIN) - you should use DefaultIfEmpty() :

 var q = from e in db.Errors from f in e.Files.DefaultIfEmpty() select new { FileId = f?.Id, ErrorId = e.Id }

If you use old C #, where is no operator ?. - should f.Id be f.Id to int? type int? ; otherwise, the FileId property of the compiler will generate an int , and an attempt will be made when attempting to write an empty value there.

There is no complete analog of the full external connection (FULL OUTER JOIN) in linq, but you can make a LEFT JOIN, and then add files separately without errors.

  • Thank you so much! This is exactly what I was looking for! - XelaNimed

Here is a selection of all files with errors.

 Files.Where(x => x.Errors.Count>0)
  • I need to select files with errors and a description of these errors. In the proposed version, we get only a list of files, without a description of errors. - XelaNimed
  • @XelaNimed all errors are in the Files[*].Errors property Files[*].Errors - Alex78191
  • I know. The problem was how to choose without using cycles. - XelaNimed
  • @XelaNimed I did not say that you need to use arrays. Than you are not satisfied with the need to address errors through a property? - Alex78191
  • "... the need to address errors through the property" - just this very. Give your example or edit an existing one that would return a List with the properties of both entities, so that it is clear what you are talking about. - XelaNimed