What's wrong on do this "basic" Entity query which result very slow?

Here's my code:

var activities = ctx.Activities.AsNoTracking();
var data = ctx.Patients.AsNoTracking().Select(p => new
{
	ID = p.ID,
	DateOfBirth = p.DateOfBirth,
	FirstName = p.FirstName,
	LastName = p.LastName,
	Gold = p.Gold,
	PromoSmiles = p.PromoSmiles,
	PatientStatusID = p.PatientStatusID,
	Activity = activities.Where(a => a.PatientID == p.ID).GroupBy(a => a.Converted).Select(a => a.FirstOrDefault()).ToList(),
	Clinic = p.Clinics
}).AsEnumerable().Select(p => new
{
	ID = p.ID,
	ClinicID = p.Clinic.ID,
	Name = p.LastName + " " + p.FirstName,
	Clinic = p.Clinic.Name,
	Date = p.DateOfBirth.HasValue ? p.DateOfBirth.Value.ToString("yyyyMMdd") : "-",
	PatientStatusID = p.PatientStatusID,
	ActivityTypeID = (p.Activity.Count == 0 ? (int?)ActivityType.NESSUNA : p.Activity.Count > 1 ? (int?)ActivityType.ENTRAMBE : p.Activity.FirstOrDefault().Converted.HasValue && p.Activity.FirstOrDefault().Converted.Value ? (int?)ActivityType.PRATICA : (int?)ActivityType.VISITA).ToString(),
	Gold = p.Gold.HasValue && p.Gold.Value ? 1 : 0,
	PromoSmiles = p.PromoSmiles.HasValue && p.PromoSmiles.Value ? 1 : 0
});

// here's I'll apply in future some further filters and orders on data

var dataTotal = data.Count();
if (formData.length >= 0)
{
	data = data.Skip(formData.start).Take(formData.length);
}
var dataFiltered = data.ToList();
return Json(new { data = dataFiltered, recordsTotal = dataTotal });

When I execute it (Patients got nearly 10k records) it takes 5-6 seconds to be executed, which is very long.

Not sure where am I wrong; I've noticed that data.Count() do a query with "all fields" of the tables, not the ones I've selected above, so maybe the lag is also because of the streaming "to server" all the records before doing count?

Also, it seems that Activity extraction could be improved maybe? I've tried some others tuning, but its takes always the same time.

Last thing: .AsEnumerable() seems overkill, but not sure why I should replace it getting Data formatted from SQL as I want. Maybe would help make custom view?

I would expect to be executed in few ms this basic query, right?

> AsEnumerable()

As long as you work on an IQueryable<> the query is still being constructed and will be sent when ready all at once. However when you force it to become an IEnumerable<> with that above line the query as is gets sent to the server, it runs and then downloads all of the data for you to use in Linq as you wish.

Any paging you apply later (Take/Skip) will be run on the in-memory copy of the entire database, not on the server using indices.

Obviously, without your database schema we cannot really tell. Could be anything.

However, I will try an educated guess:

The fact that you have no navigation property of "Activities" on "Patient" leads me to believe that whether you are using code first or database first, there probably is no foreign key on "PatientID" of "Activities". Because it would only get there through the navigation property in code first, or a navigation property would have been created automatically with database first. With no foreign key, the chance of there being a lone random index that just magically appeared is also slim. That most likely means for each patient, the activities table gets a full table scan. That is slow.

The solution? Give [Activity].[PatientID] a foreign key connecting it to the primary key of [Patient].[ID] through whatever means you use to create your database and make sure it is reflected in your context if you aren't using code first.