user3754008 user3754008 - 1 month ago 16
C# Question

Convert yyyyMMdd to date time in linq select

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

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();
Comments