0

I am working in an MVC controller and getting this error. I know that I can't cast a string to date within a linq query, but I was wondering if someone can help me with a workaround for this. My model collects the date from the database as a string and I MUST keep it this way instead of setting it as DateTime in my model, but I did create a ViewModel that allows for conversion to DateTime first so that I can run the needed query.

My Model:

namespace advertise.Models
{
    public class Advertise_Clicks
    {
        public int ID { get; set; }
        public int bannerID { get; set; }
        public int Clicks { get; set; }
        public string ClickDate { get; set; } 
    }
}

My ViewModel:

namespace advertise.ViewModels
{
    public class AdvertiseClicksTotals
    {
        public int bannerID { get; set; }
        public int Clicks { get; set; }
        public string ClickDateStr { get; set; }

        public DateTime ClickDate 
        { 
           get
           {
              return (DateTime)MySqlCDateTimeUtil.MySQLDateStringToNullableDateTime(ClickDateStr); 
           }
        }
    }
}

My code generating error:

string[] arrCurrentMonthDateRange = MySqlCDateTimeUtil.GetMySQLDateRange(now);
DateTime statsStartDate = (DateTime)MySqlCDateTimeUtil.MySQLDateStringToNullableDateTime(arrCurrentMonthDateRange[0]);
DateTime statsEndDate = (DateTime)MySqlCDateTimeUtil.MySQLDateStringToNullableDateTime(arrCurrentMonthDateRange[1]);  

var banners = from b in db.Banners
where b.ad_userID == userID
select new BannersData
{
    ID = b.ID,
    BannerName = b.BannerName,
    LinkImage = b.LinkImage,
    ImageBorder = b.ImageBorder,
    Redirect = b.Redirect,
    BActive = b.BActive,
    Approved = b.Approved,
    Alt = b.Alt,
    Expires = b.Expires,
    BannerTypeID = b.BannerTypeID,
    ApprovedBool = b.Approved == 1,
    ActiveBool = b.BActive == 1,
    ImageBorderBool = b.ImageBorder == 1,
    DisplayName = b.AdvertiseBanner.DisplayName,
    TotalClicks = advertiseClicksTotals.ToList().Where(ac => ac.bannerID == b.ID && Convert.ToDateTime(ac.ClickDateStr) >= statsStartDate && Convert.ToDateTime(ac.ClickDateStr) <= statsEndDate).Sum(ac => ac.Clicks)
};

**The error is because of this part of the code

TotalClicks = advertiseClicksTotals.Where(ac => ac.bannerID == b.ID && ac.ClickDate >= statsStartDate && ac.ClickDate <= statsEndDate).Sum(ac => ac.Clicks)

Is there a way to get around this? (Please let me know if you need more code)

C-R Glenn
  • 13
  • 4
  • Simple answer: `ClickDate` in `AdvertiseClicksTotals` cannot be used in `Where` since it is not mapped to a database field (declared as a `get` only property). You need another property which was part of database field to get in `Where` clause. – Tetsuya Yamamoto Feb 09 '17 at 04:53
  • Your `ac.ClickDate >= statsStartDate && ac.ClickDate <= statsEndDate` makes no sense - they are `string` not `DateTime` and that wont return anything meaningful –  Feb 09 '17 at 04:54
  • You would need to materialize your `Banner` collection to an in-memory set (using `.ToList()`) then convert the values to `DateTime` and then use your `Where()` clause in order to select values between the 2 dates. –  Feb 09 '17 at 05:00
  • Both `statsStartDate` and `statsEndDate` are `DateTime` (note that OP wants to compare DateTime value), and OP refers to `AdvertiseClicksTotals.ClickDate` (DateTime) instead of `Advertise_Clicks.ClickDate (string)`. The `AdvertiseClicksTotals.ClickDate` obviously a getter property and possibly not mapped as a DB field. – Tetsuya Yamamoto Feb 09 '17 at 05:00
  • @TetsuyaYamamoto, Not when they are quoted (and it throws an exception) . And `advertiseClicksTotals` has not even been initialized so who knows what the real code OP is using :) –  Feb 09 '17 at 05:11
  • My startStartDate and enddates are real datetimes in my real code sorry, I just plugged in strings in this post to reduce time. – C-R Glenn Feb 09 '17 at 05:12
  • I used ToList() (see my updated code above) and I'm getting a new error: LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression. What am I doing wrong? – C-R Glenn Feb 09 '17 at 13:15
  • I was able to solve this by converting the banners query tolist() as well: – C-R Glenn Feb 09 '17 at 13:55
  • var bannerDB = from b in db.Banners where b.ad_userID == userID select b; var banners = from b in bannerDB.ToList() select new BannersData { ID = b.ID, BannerName = b.BannerName, etc. (Thanks for all your input) – C-R Glenn Feb 09 '17 at 13:56

0 Answers0