Pfthb

I have the following situation which, works fine:

IQueryable<Experiment> experiments1 = _db.Experiments.Where(e => e.Projects.Any(p => p.Id == project.Id));
IQueryable<Experiment> experiments2 = _db.Experiments.Where(e => e.Tools.Any(m => m.Project.Id == project.Id));
var experimentsList = experiments1.Union(experiments2).OrderBy(e => e.Date).Select(e => new
 
 e.Id,
 e.Name,
 e.Date
 ).ToList();

Checking with SQL Profiles, this translates in 1 SQL query which correctly extract Id, Name and Date from the union.

The problem arises when I perform a join with a many-to-many connected table:

var experimentsList = experiments1.Union(experiments2).OrderBy(e => e.Date).Select(e => new

 e.Id,
 e.Name,
 e.Date,
 e.Types.Select(t => t.Name)
 // or also just e.Types
).ToList();

The generated SQL query for some reasons not only return the "needed" columns but returns all the columns in the Experiment table + the additional specified columns (i.e. types' names).

This result in a pretty big loss in performances.

Btw, in the code I later need to perform:

experimentsList.Select(e => new

 e.Id,
 e.Name,
 e.Date,
 Types = string.Join(", ", e.Types)
)

So I need the faster way to have in memory Id, Name, Date + a list of types.

Thanks!

Can you try this? Coloum names may need to be changed

var result = from exp in _db.Experiments
join t in _db.Types on exp.TypeID = t.Id
select new

 exp.Id,
 exp.Name,
 exp.Date,
 t.Name
).ToList();