CODEWITHSUNDEEP
c#.netdatelinq-to-sql
serhads
serhads

Reputation: 462

linq to sql nullable date issue

int ddlshopID = ddlSupervisorShop.SelectedItem == null ? 0 : ddlSupervisorShop.SelectedValue.ToInt32();
DateTime today = DateTime.Now;
List<int> supervisorShopList = shops.Select(a => a.ShopID).ToList<int>();
totalSales = (from ds in db.DailySales
              join p in db.Products on ds.ProductID equals p.ProductID
              where ds.IsActive == true && 
                    p.IsActive == true && 
                    ds.SaleDate.Value.Month == today.Month && 
                    ds.SaleDate.Value.Year == today.Year &&
                    ddlshopID == 0 ? supervisorShopList.Contains(ds.ShopID) : ds.ShopID == ddlshopID
              group ds by new
              {
                  p.ProductCategoryID,
                  p.IsActive,
              }
              into g
              select new TotalPriceDivision
              {
                  TotalPrice = g.Sum(a => a.Quantity * a.PerPrice),
                  DivisionID = g.Key.ProductCategoryID
              }).ToList<TotalPriceDivision>();

In this piece of code the line 

ds.SaleDate.Value.Month == today.Month && ds.SaleDate.Value.Year == today.Year

doesn't affect query result. ds.SaleDate is nullable date value (not datetime) in the database. Is it because of that? If yes, how can I solve this?

Upvotes: 0

Views: 247

Answers (2)

serhads
serhads

Reputation: 462

I've found what is wrong with this.

surprisingly,incredibly and stupidly

if I order my query as 

where ds.IsActive == true
&& p.IsActive == true
&& ds.SaleDate.Value.Month == today.Month
&& ds.SaleDate.Value.Year == today.Year
&& ddlshopID == 0 ? supervisorShopList.Contains(ds.ShopID) : ds.ShopID == ddlshopID

date conditions doesn't effect query result and it returns wrong.

İf I order it as 

where ds.IsActive == true
&& p.IsActive == true
&& ddlshopID == 0 ? supervisorShopList.Contains(ds.ShopID) : ds.ShopID == ddlshopID
&& ds.SaleDate.Value.Month == today.Month
&& ds.SaleDate.Value.Year == today.Year

it works as I expected

I still can't beleive how is it possible. And if you think there must be another reason please explain me.

Upvotes: 0

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

where ds.IsActive && // don't compare boolean with true
      p.IsActive &&
      ds.SaleDate.HasValue && // add this condition
      ds.SaleDate.Value.Month == today.Month && 
      ds.SaleDate.Value.Year == today.Year &&
      ddlshopID == 0 ? supervisorShopList.Contains(ds.ShopID) : ds.ShopID == ddlshopID

Upvotes: 1

Related Questions