Linq To Entities Query

Question

Consider the following query:

var profilelst = ( from i in dbContext.ProspectProfiles where i.CreateId == currentUser select new ProspectProfile { ProspectId = i.ProspectId, Live = i.Live, Name = i.Name, ServiceETA = i.Opportunities.OrderByDescending(t => t.FollowUpDate) .FirstOrDefault() .ServiceETA.ToString(), FollowUpDate = i.Opportunities.OrderByDescending(t => t.FollowUpDate) .FirstOrDefault() .FollowUpDate } ) .ToList(); return profilelst.OrderByDescending(c=>c.FollowUpDate) .Skip(0).Take(endIndex) .ToList(); 

In this question, please take a look at FollowUpDate and ServiceType , I both got these from the Opportunity table, is there any other work to get these both.

One to Large Interaction in Tables is as follows: ProspectProfile -> Opportunities

Is the request that I wrote is approved or is there some other work that could be done in a simpler way.

Answer 1

The only thing you can improve is not to order twice by changing the code:

 var profilelst = dbContext.ProspectProfiles .Where(i => i.CreateId == currentUser) .Select(i => { var opportunity = i.Opportunities .OrderByDescending(t => t.FollowUpDate) .First(); return new ProspectProfile { ProspectId = i.ProspectId, Live = i.Live, Name = i.Name, ServiceETA = opportunity.ServiceETA.ToString(), FollowUpDate = opportunity.FollowUpDate } }).ToList(); return profilelst.OrderByDescending(c => c.FollowUpDate).Take(endIndex).ToList(); 

I made a few changes to the original request:

• I modified it to use method chain syntax. In my opinion, reading is much easier.
• I removed the unnecessary Skip(0) .
• The biggest change in the Select part:
  • I changed FirstOrDefault to First because you are still accessing the properties of the return value. This will raise a descriptive exception if not possible. This is better than yours: in your case, he would NullReferenceException . This is bad, NullReferenceExceptions always indicates an error in your program and is not descriptive at all.
  • I moved the part that selects the opportunity from the initializer, so we need to do the sorting only once, not twice.

Answer 2

There are quite a few problems in your request:

• You cannot design an object ( select new ProspectProfile ). LINQ to Entities only supports predictions in anonymous types ( select new ) or other types that are not part of your entity data model ( select new MySpecialType )

• ToString() for a numeric or DateTime type is not supported in LINQ to Entities ( ServiceETA.ToString() )

• FirstOrDefault().ServiceETA (or FollowUpdate ) throws an exception if the Opportunities collection is empty and ServiceETA is a non-nullable value type (such as DateTime ) because EF cannot materialize the value into such a variable.

• Using .ToList() after your first query executes the query in the database and loads the full result. Your later Take occurs in memory in the full list, and not in the database. (You effectively load the entire list of results from the database into memory and then discard all objects except the first, you have Take en.

To fix all four problems, you can try the following:

 var profilelst = dbContext.ProspectProfiles .Where(p => p.CreateId == currentUser) .Select(p => new { ProspectId = p.ProspectId, Live = p.Live, Name = p.Name, LastOpportunity = p.Opportunities .OrderByDescending(o => o.FollowUpDate) .Select(o => new { ServiceETA = o.ServiceETA, FollowUpDate = o.FollowUpDate }) .FirstOrDefault() }) .OrderByDescending(x => x.LastOpportunity.FollowUpDate) .Skip(startIndex) // can be removed if startIndex is 0 .Take(endIndex) .ToList(); 

This will give you a list of anonymous objects. If you need a result in the list of your ProspectProfile object, you must copy the values ​​after this query. Note that LastOpportunity may be null as a result if a ProspectProfile does not have Opportunities .