I use it out of habit… But I also have a habit of always doing Db.Table.With(LockHint.NoLock). If you ask the LINQ/EF crowd that actually deal with LINQ in more ways than we will ever in Epicor, that understand Lazy, Eager loading - they would prob advice against it. But when it comes to BPMs I found it easier and faster to work with .ToList() in Memory and not have alot of SQL Calls.
As an aside reminder also never directly join a tt table and db table together either. It will first pull the entire DB table 100% then do the join in object memory land.
If we counted the carbon emission SELECT * causes in a year, there would a revolution to safe the planet. All that wasted compute power. If not for yourself, do it for the planet.
I have been converting almost 100% of my c# queries to Lamda expressions… once I figured out the protocol, it made more sense to me… A sample of the type of query I find needing alot is below:
//to find if a part exists:
bool partExists = Db.Part.Any(x=>x.Company == CompanyID && x.PartNum == myPart);
//to retrieve just the part's description:
string partDesc = Db.Part.Where(x=>x.Company == CompanyID && x.PartNum == myPart).Select(x=>x.PartDescription).FirstOrDefault() ?? "Not Found";
//to retrieve multiple field values:
var PRecord = Db.Part.Where(x=>x.Company == CompanyID && x.PartNum == myPart).Select(x=> new {x.PartDescription,x.IUOM}).FirstOrDefault();
//remember... we need to check for PRecord is null before processing
I think I have an internal paper from the early days of E10 - and Entity Framework to be honest. We worked with the EF team at MSFT and their architect at the time and us did the initial Data Layer for E10 and proofed out auto-compilation and a bunch of things that eventually made it into EF.
A couple of things to understand about ToList()
ToList 'resolves the query’
That means it grabs all the data, pulls it to the ‘client’ (e.g. the app server). This can be wanted or a horrible idea.
For example, we use this in the data layer to prevent needless db queries and provides the perf boost you get in E10 over E9. If you are in a transaction, make the same query with the same parameters over and over and over again, just use the last db query. (I am looking at all code in data triggers and data directives that will never abuse the db versus E9). When the transaction completes, the result cache is cleared. That simple behavior made HUGE perf and scale gains in 9 vs 10.
The bad?
If you are looking at a huge list of data that you intend to pull to the app server and then compared to a list in memory - horrible idea. I think someone mentioned the joining of ttRows and DB Rows as an example of that.
I would love to have a batter solution available in a widget but the best approach on those scenario is similar to what was done in the Stored Procedures. Query for the data in the db and cache it in SQL memory or in a db table i you have to. Then join against that data. This performs much quicker.
