I've been working a lot more with LINQ2SQL and LINQ2Entities lately, and I ran into my first issue at the end of last week. I couldn't figure out why null parameters were causing the query to return strange results.
The original query looked like this:
List<MenuLayoutDataEntity> ret = (from c in _Context.tblMenuLayouts
where c.ConceptID == entity.ConceptID
&& c.ItemID == entity.ItemID
&& c.MarketID == entity.MarketID
&& c.DepartmentID == entity.DepartmentID
orderby c.SortOrder
select new MenuLayoutDataEntity()
{
MenuID = c.MenuID,
ItemNamePOS = c.ItemNamePOS,
ItemID = c.ItemID.HasValue ? c.ItemID.Value : -1
}).ToList();Sometimes, the MarketID and DepartmentID would be null, and that's what was causing the problem. After some Googling, I found that I need to enclose the equality using the Equals method in the object class. Here's the query using Equals:
List<MenuLayoutDataEntity> ret = (from c in _Context.tblMenuLayouts
where c.ConceptID == entity.ConceptID
&& c.ItemID == entity.ItemID
&& Equals(c.MarketID, entity.MarketID)
&& Equals(c.DepartmentID, entity.DepartmentID)
orderby c.SortOrder
select new MenuLayoutDataEntity()
{
MenuID = c.MenuID,
ItemNamePOS = c.ItemNamePOS,
ItemID = c.ItemID.HasValue ? c.ItemID.Value : -1
}).ToList();Unfortunately, this only works for LINQ2SQL, and this query is actually a LINQ2Entities query. So back to Googling. I finally found a hint in a post over at StackOverflow. Apparently, there's is a bug in the LINQ2Entities parser when trying to use IS NULL functionality in the where clause. The solution was to check the value on the entity class, and then select a specific query. Here's the results that actually gave me the results that I wanted:
List<MenuLayoutDataEntity> ret = null;
// Check if this is a store item, i.e. Departmental
if (entity.MarketID.HasValue && entity.DepartmentID.HasValue)
{
ret = (from c in _Context.tblMenuLayouts
where c.ConceptID == entity.ConceptID
&& c.ItemID == entity.ItemID
&& c.MarketID == entity.MarketID
&& c.DepartmentID == entity.DepartmentID
orderby c.SortOrder
select new MenuLayoutDataEntity()
{
MenuID = c.MenuID,
ItemNamePOS = c.ItemNamePOS,
ItemID = c.ItemID.HasValue ? c.ItemID.Value : -1
}).Distinct().ToList();
}
// Check if this is a market item, i.e. MarketID is not null
if (entity.MarketID.HasValue && !entity.DepartmentID.HasValue)
{
ret = (from c in _Context.tblMenuLayouts
where c.ConceptID == entity.ConceptID
&& c.ItemID == entity.ItemID
&& c.MarketID == entity.MarketID
&& c.DepartmentID == null
orderby c.SortOrder
select new MenuLayoutDataEntity()
{
MenuID = c.MenuID,
ItemNamePOS = c.ItemNamePOS,
ItemID = c.ItemID.HasValue ? c.ItemID.Value : -1
}).Distinct().ToList();
}
// Check if this is a global item, i.e. both MarketID and DepartmentID are null
if (!entity.MarketID.HasValue && !entity.DepartmentID.HasValue)
{
ret = (from c in _Context.tblMenuLayouts
where c.ConceptID == entity.ConceptID
&& c.ItemID == entity.ItemID
&& c.MarketID == null
&& c.DepartmentID == null
orderby c.SortOrder
select new MenuLayoutDataEntity()
{
MenuID = c.MenuID,
ItemNamePOS = c.ItemNamePOS,
ItemID = c.ItemID.HasValue ? c.ItemID.Value : -1
}).Distinct().ToList();
}Very annoying, but at least I know how to handle this. Hopefully this will help another soul out there.
