Convert yyyyMMdd to date time in linq select

Question

Is it a way to convert a string of yyyyMMdd to date time in linq select.

1 - I try Convert.toDateTime(), DateTime.ParseExact(), DateTime.Parse(). All of them give me error.

Error Message are similar to this.

Additional information: LINQ to Entities does not recognize the method 'System.DateTime Parse(System.String)' method, and this method cannot be translated into a store expression.

2 - I can be sure those data need to convert to date validate date with yyyyMMdd format.

Please see my following code to understand what exactly do i mean.

return (from p in db.ExchangeDatas
            where p.ExchangeDataSeqid == entity.ExchangeDataSeqid
            select new ProcessAccountViewModel()
            {
                ExchangeCode = p.ExchangeCode,
                UtilityCompany = p.UtilityCompanySeqid,
                InvoiceBillingGroup = p.AccountBillingGroupSeqid,
                AccountNumber = p.CurrentAccountNumber,
                TurnOnDate = DateTime.ParseExact(p.AccountEffectiveTurnOn, "yyyyMMdd", CultureInfo.InvariantCulture),
                SalesType = p.SalesType,
                BillingCycle = p.BillingCycle,
                TripNumber = p.TripNumber,
                IsTimeOfDay = p.TODAccount == "Y" ? true : false,
                IsExcessDistribution = p.ExcessDistributionAccount == "Y" ? true : false,
                EnergyDeliverType = p.EnergyDeliveryType ?? 0,
                Name = p.AccountName,
                Address = p.AccountAddress,
                Borough = p.Borough,
                Facility = p.FacilitySeqid == null ? "" : p.FacilitySeqid.Value.ToString(),
                Agency = p.AgencySeqid == null ? "" : p.AgencySeqid.Value.ToString(),
                ServiceClass = p.DeliveryServiceClass,
                AuthenticatedUserID = p.authenticatedUserID ?? 0,
                ApprovedForCreation = p.ApprovedForCreation,
                TransactionEffectiveDate = DateTime.ParseExact(p.TransactionEffectiveDate, "yyyyMMdd", CultureInfo.InvariantCulture),
                ActivityTime = DateTime.ParseExact(p.ActivityTime, "yyyyMMdd", CultureInfo.InvariantCulture),
                DateAdded = p.DateAdded,
                LastUpdate = p.LastUpdate,
                Exclude = p.Exclude,
                IsProcessed = p.IsProcessed,
                BillingPeriod = p.BillingPeriod
            }).FirstOrDefault();

Answer 1

I'm not sure if this will work, but try adding some logic in your ProcessAccountViewModel to handle that for you. Something like this:

class ProcessAccountViewModel()
{
   ...
   DateTime TransactionEffectiveDate { get; set; } // you already have this
   string TransactionEffectiveDateAsString // add this
   {
       set
       {
           TransactionEffectiveDate = DateTime.ParseExact(value,
                      "yyyyMMdd", CultureInfo.InvariantCulture);
       }
   }
}

Then, instead of setting TransactionEffectiveDate directly in your LINQ query, use the string version:

So, instead of:

TransactionEffectiveDate = DateTime.ParseExact(p.TransactionEffectiveDate,
                                "yyyyMMdd", CultureInfo.InvariantCulture),

Do

TransactionEffectiveDateAsString = p.TransactionEffectiveDate,

Answer 2

LINQ to enitites does not support DateTime.ParseExact method so you can try to bring the collection you need into memory by using AsEnumerable(), and then do all the parsing using LINQ to Objects.

maybe this will work?

var exchangeDatas = from p in db.ExchangeDatas
                    where p.ExchangeDataSeqid == entity.ExchangeDataSeqid
                    select p;

return (from p in exchangeDatas.AsEnumerable() 
                  select new ProcessAccountViewModel()
                  {
                      ExchangeCode = p.ExchangeCode,
                      UtilityCompany = p.UtilityCompanySeqid,
                      InvoiceBillingGroup = p.AccountBillingGroupSeqid,
                      AccountNumber = p.CurrentAccountNumber,
                      TurnOnDate = DateTime.ParseExact(p.AccountEffectiveTurnOn, "yyyyMMdd", CultureInfo.InvariantCulture),
                      SalesType = p.SalesType,
                      BillingCycle = p.BillingCycle,
                      TripNumber = p.TripNumber,
                      IsTimeOfDay = p.TODAccount == "Y" ? true : false,
                      IsExcessDistribution = p.ExcessDistributionAccount == "Y" ? true : false,
                      EnergyDeliverType = p.EnergyDeliveryType ?? 0,
                      Name = p.AccountName,
                      Address = p.AccountAddress,
                      Borough = p.Borough,
                      Facility = p.FacilitySeqid == null ? "" : p.FacilitySeqid.Value.ToString(),
                      Agency = p.AgencySeqid == null ? "" : p.AgencySeqid.Value.ToString(),
                      ServiceClass = p.DeliveryServiceClass,
                     AuthenticatedUserID = p.authenticatedUserID ?? 0,
                     ApprovedForCreation = p.ApprovedForCreation,
                     TransactionEffectiveDate = DateTime.ParseExact(p.TransactionEffectiveDate, "yyyyMMdd", CultureInfo.InvariantCulture),
                     ActivityTime = DateTime.ParseExact(p.ActivityTime, "yyyyMMdd", CultureInfo.InvariantCulture),
                     DateAdded = p.DateAdded,
                     LastUpdate = p.LastUpdate,
                     Exclude = p.Exclude,
                     IsProcessed = p.IsProcessed,
                     BillingPeriod = p.BillingPeriod
                 }).FirstOrDefault();