I'm using EF Core. I have about 2500 records in database table Articles.
In management studio with EF compiled SQL I get result in few MS. In program about 30secs. .Select() is the issue. I don't know how to optimize it anymore.
1: before
List<Article> articles = await db.Articles.Select(x => new Article { Title = x.Title, Description = x.Description, Body = x.Body, Authors = x.Authors, PhotoAuthors = x.PhotoAuthors, Tags = x.Tags }).ToListAsync();
2: now
List<Article> articles = await db.Articles.FromSql("SELECT [x].[Title], [x].[Description], [x].[Body], [x].[Authors], [x].[PhotoAuthors], [x].[Tags] FROM[Articles] AS[x]").Select(x => new Article { Title = x.Title, Description = x.Description, Body = x.Body, Authors = x.Authors, PhotoAuthors = x.PhotoAuthors, Tags = x.Tags }).ToListAsync();
Result is still same :/
EDIT: Solution is to not use .ToList()
Using:
IQueryable<Article> articles = db.Articles.Select(x => new Article { Title = x.Title, Description = x.Description, Body = x.Body, Authors = x.Authors, PhotoAuthors = x.PhotoAuthors, Tags = x.Tags }).AsNoTracking();
IQueryable lose functions like .Split which I used later or Intersects.
I think you're over thinking it.
var articles = db.Articles.ToList();
gets you a list of articles. If you really don't want to select extra fields, then you could do a
var articles = db.Articles.Select(x => new {...}).ToList();
but generally speaking you shouldn't see much of a difference between grabbing the entirety of vs just some of the headers. What you probably need is a .Where
to limit things, but if you really need it all, just keep it simple.