I have been refactoring some of our clunky and not working LINQ queries lately when I find issues in our project. Here is one that is throwing me for a loop. I must confess, I am an android developer catapulted into C# with entity framework so I don't truly understand what is happening here. Nevertheless these queries look nearly the same to me (sprinkle a subquery here and a new join there). The first method will return without error:
public virtual IQueryable<ScratchGameDetailsModel> GetScratchGameDetails(string reconGUID)
{
var lotteryReconHeader = _lotteryReconHeaderRepository.GetAll().AsEnumerable(); // Changed on Nov 7,2017
var lotteryReconDetails = _lotteryReconDetailRepository.GetAll().Where(s => s.ReconGUID == reconGUID).AsEnumerable();
var lotteryGames = _lotteryGameRepository.GetAll().AsEnumerable();
var lotteryActivations = _lotteryActivationRepository.GetAll().AsEnumerable();
var response = (from ltrd in lotteryReconDetails
join lg in lotteryGames on ltrd.GameGUID equals lg.GameGUID
join la in lotteryActivations on lg.GameGUID equals la.GameGUID into LotteryActivations_join
from la in LotteryActivations_join.DefaultIfEmpty()
join ltrh in lotteryReconHeader on ltrd.ReconGUID equals ltrh.ReconGUID // Added on Nov 7,2017
select new ScratchGameDetailsModel()
{
ReconGUID = ltrd.ReconGUID,
GameGUID = ltrd.GameGUID,
GameNumber = lg.GameNumber ?? Constants.Zero,
GameName = lg.GameName ?? String.Empty,
GameUPC = lg.GameUPC ?? string.Empty,
GameBin = lg.GameBin ?? Constants.Zero,
StartCount = ltrd.StartCount,
EndCount = ltrd.EndCount,
CashierSales = ltrd.CashierSales,
ExpectedStartCount = ltrd.ExpectedStartCount,
QtyOnRoll = lg.QtyOnRoll ?? Constants.Zero,
BookNumber = (la == null || la.BookNumber == null ? string.Empty : la.BookNumber.Split(new char[] { '-' }, 2).ElementAt(1).TrimStart('-')),
ReconTimeStamp = ltrh.ReconTimeStamp // Added on Nov 7,2017
});
return response.AsQueryable();
}
You can probably tell why I don't like this method, 3 of those ienumerable lists are complete dumps of the DB tables of which later is joined. When the DB grows the query time of those variables increases. Complete waste of resources if you ask me. I have already fixed some methods that took over 5 minutes to populate data on another table due to the massive variable declaration and they are now being done in <1ms.
Here is the replacement method which should return all the same except now there is a subquery included. It also no longer sets ienumerable lists and just handles the query using DB context. This throws an error stating:
The method 'System.String TrimStart(Char[])' is only supported in LINQ to Entities when there are no trim characters specified as arguments.
public virtual IQueryable<ScratchGameDetailsModel> GetScratchGameDetailsNew(string reconGUID)
{
var newResponse = (from ltrd in _context.LotteryReconDetail
join lg in _context.LotteryGame on ltrd.GameGUID equals lg.GameGUID
join la in _context.LotteryActivation
on new
{
lg.GameGUID,
BookNumber =
((from la in _context.LotteryActivation
where la.GameGUID == lg.GameGUID
orderby la.row_id descending
select new { la.BookNumber }).FirstOrDefault().BookNumber)
}
equals new { la.GameGUID, la.BookNumber } into la_join
from la in la_join.DefaultIfEmpty()
join ltrh in _context.LotteryReconHeader on ltrd.ReconGUID equals ltrh.ReconGUID
where ltrd.ReconGUID == reconGUID
select new ScratchGameDetailsModel()
{
ReconGUID = ltrd.ReconGUID,
GameGUID = ltrd.GameGUID,
GameNumber = lg.GameNumber ?? Constants.Zero,
GameName = lg.GameName ?? String.Empty,
GameUPC = lg.GameUPC ?? string.Empty,
GameBin = lg.GameBin ?? Constants.Zero,
StartCount = ltrd.StartCount,
EndCount = ltrd.EndCount,
CashierSales = ltrd.CashierSales,
ExpectedStartCount = ltrd.ExpectedStartCount,
QtyOnRoll = lg.QtyOnRoll ?? Constants.Zero,
BookNumber = (la == null || la.BookNumber == null ? string.Empty : la.BookNumber.Split(new char[] { '-' }, 2).ElementAt(1).TrimStart('-')),
ReconTimeStamp = ltrh.ReconTimeStamp
});
return newResponse.AsQueryable();
}
With my method, if I use the same operations (like setting variables) and use those for the query I receive "Object reference not set to an instance of an object." Eureka! But why? The same raw SQL query has no issues and I would greatly like to know what the difference in LINQ is using DB context vs a queryable/enumerable context.
I have also modified those variable declarations and used AsQueryable() instead of AsEnumerable() so the entire list is not dumped but rather used as DB context in the query. If I am not mistaken that's the noticable difference between enumerable and queryable.
This was resolved as @TheGeneral had pointed out. The calling assignment was updated to handle the value trimming as needed.
BookNumber = (la == null || la.BookNumber == null ? string.Empty : la.BookNumber),
Then updated the calling method to handle the trim
var scratchGameDetails = _lotteryReconService.GetScratchGameDetailsNew(reconGUID).ToList().Select(s => new
{
s.ReconGUID,
s.GameGUID,
s.GameNumber,
s.GameName,
s.GameUPC,
s.GameBin,
s.StartCount,
s.EndCount,
s.CashierSales,
s.ExpectedStartCount,
RangeStart = gameStartCount,
RangeEnd = useBaseZero ? (s.QtyOnRoll - 1) : s.QtyOnRoll,
BookNumber = (s.BookNumber == string.Empty ? string.Empty : s.BookNumber.Split(new char[] { '-' }, 2).ElementAt(1).TrimStart('-')),
s.ReconTimeStamp
}).OrderByDescending(x => x.ReconTimeStamp).Distinct();
