none
Convert int to DateTime Linq where Clause EF6 RRS feed

  • Question

  • I have many int from a Data Ware house which are int. I am creating a report and needs to convert this int to DateTime in the where clause. I tried adding an entry in my original entity class like this

    		public DateTime Date1
    		{
    			get { return Convert.ToDateTime(CNTRDraft_Submit); }
    		}

    and calling it in code like this 

    var reportCdrl = (from v in data.DimContracts
    											from h in v.DimCDRLHeaders
    											from w in v.DimWDTOes
    											from c in h.FactDeliverables
    											from d in c.FactApprovalStatuses
    							where v.ContractKey == contractID &&
    								((((c.Date1) >= beginDateRange &&
    								   (c.Date1) <= endDateRange) ||
    								  ((c.Date3) >= beginDateRange &&
    								   (c.Date3) <= endDateRange) ||
    								  ((d.Date11) >= beginDateRange &&
    								   (d.Date11) <= endDateRange) ||

    But is not working. How do I convert the int to datetime in EF 6

     

    Ebenezer

    Tuesday, October 4, 2016 7:31 PM

Answers

  • Hi David

    Thanks a lot. Your solution works and made things simpler.


    Ebenezer

    • Marked as answer by denkyira Wednesday, October 5, 2016 5:07 PM
    Wednesday, October 5, 2016 5:07 PM

All replies

  • You don't want to do that, even if it worked.  You don't want to send SQL a query that requires row data to be converted for comparison. 

    If the DW is using "smart keys", ie 20160203 for Feb 3, 2016, then just convert your beginDateRange and endDateRange to int and use that in the query.

    David

     

    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, October 4, 2016 7:55 PM
  • Hi David

    Thanks for your response. I need to  explain my issue more in detail. My DW is using  "smart keys". The problem is, I have lots of Dates which are mapped to Date Dimension keys and therefore they are all  int. I need a way to cast them back to DimDate Date. I am not using SSAS but rather Linq query to read the data into DataTable. This is a full portion of my code

    public DataTable getLateActivityReportData(int contractID, string cdrlName, string cdrlNumber, DateTime beginDateRange, DateTime endDateRange, bool includeCNTR, bool includeUSG, bool includeInProgress)
    		{
    			//get all deliverables for specified contract
    			var cdrl = data.FactDeliverables.Where(c => c.DimCDRLHeader.DimContract.ContractKey == contractID);
    			var approval = new List<FactApprovalStatus>();
    
    		    if (includeInProgress)
    		        cdrl = cdrl.Where(c => c.USG_ReviewDate_Actual == null);
    
    			if (includeCNTR == true && includeUSG == true)
    			{
    				var reportCdrl = (from c in cdrl.ToList()
    								  from d in approval.ToList()
    								  where
    									  (((Convert.ToDateTime(c.CNTRDraft_Submit) >= beginDateRange &&
    										 Convert.ToDateTime(c.CNTRDraft_Submit) <= endDateRange) ||
    										(Convert.ToDateTime(c.CNTRFinal_Submit) >= beginDateRange &&
    										 Convert.ToDateTime(c.CNTRFinal_Submit) <= endDateRange) ||
    										(Convert.ToDateTime(d.USGApprovalDate) >= beginDateRange &&
    										 Convert.ToDateTime(d.USGApprovalDate) <= endDateRange) ||
    										(Convert.ToDateTime(d.USGReviewDate) >= beginDateRange && Convert.ToDateTime(d.USGReviewDate) <= endDateRange)) &&
    									   (
    										   (c.IsDraft &&
    											(((c.CNTR_DraftSubmitDate_Actual == null && Convert.ToDateTime(c.CNTRDraft_Submit) < DateTime.Today) ||
    											  (c.USG_ReviewDate_Actual == null
    											   && Convert.ToDateTime(c.USGDraft_Review) < DateTime.Today))
    									  //If there is a submit time, and it was past due
    											 || c.CNTR_DraftSubmitDate_Actual > c.CNTRDraft_Submit
    											 || c.USG_ReviewDate_Actual > c.USGDraft_Review))
    									  //If there no submit time, and it is past due
    										   || (c.CNTR_FinalSubmitDate_Actual == null && Convert.ToDateTime(c.CNTRFinal_Submit) < DateTime.Today)
    										   || (c.USG_ApprovalDate_Actual == null && Convert.ToDateTime(c.USGFinal_Review) < DateTime.Today)
    									  //If there is a submit time, and it was past due
    										   || c.CNTR_FinalSubmitDate_Actual > c.CNTRFinal_Submit
    										   || c.USG_ApprovalDate_Actual > c.USGFinal_Review))
    
    								  orderby c.DimCDRLHeader.DimContract.ContractGroupLabel, c.DimCDRLHeader.CDRLNumber, c.DimCDRLHeader.CDRLName
    								  let wdtoPopStartDate = c.DimCDRLHeader.DimWDTO.WDTO_POP_StartDate
    								  where wdtoPopStartDate != null
    								  let wdtoPopEndDate = c.DimCDRLHeader.DimWDTO.WDTO_POP_EndDate
    								  where wdtoPopEndDate != null
    								  select new
    								  {
    									  ContractPlatform_STR = c.DimCDRLHeader.DimContract.ContractGroupLabel,
    									  WDTOPlatform_STR =
    										  c.DimCDRLHeader.DimWDTO.WDTONumber + "/" + c.DimCDRLHeader.DimWDTO.WDTOName + " (" +
    										  wdtoPopStartDate.Value.ToString("dd MMM yy") + " - " + wdtoPopEndDate.Value.ToString("dd MMM yy"),
    									  WDTOID = c.DimCDRLHeader.DimWDTO.WDTOId,
    									  CDRLHeader = c.DimCDRLHeader.CDRLNumber + " - " + c.DimCDRLHeader.CDRLName,
    									  c.CNTRDraft_Submit,
    									  c.USGDraft_Review,
    									  c.CNTRFinal_Submit,
    									  c.USGFinal_Review,
    									  c.NoLongerRequired
    
    
    								  }
    					).Distinct().AsQueryable();
    Now CNTRDraft_Submit in the DW date field is for example '20161006' and c.CNTR_FinalSubmitDate_Actual is '20160920' I need to cast these smart keys to the actual Date for display.


    Ebenezer


    • Edited by denkyira Wednesday, October 5, 2016 12:29 AM
    Wednesday, October 5, 2016 12:28 AM
  • You can convert the int's to DateTime after retrieving the results from the database, and the extra properties you suggested are a good way to do that.  But you cannot use the converted values in the database query.  You should use the int's there.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, October 5, 2016 1:04 AM
  • Thanks David. I will keep you posted tomorrow morning. I get to work by 5:00 AM Eastern.


    Ebenezer

    Wednesday, October 5, 2016 1:06 AM
  • Hi denkyira,

    According to your description, it seems that we could use DateTime.ParseExact to implement it. like this:

    (((DateTime.ParseExact(c.CNTRDraft_Submit.ToString(), "yyyyMMdd", null) >= beginDateRange &&
    // null means current-culture

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 5, 2016 1:40 AM
    Moderator
  • Hi Cole

    Thanks for your response. I will Implement it tomorrow morning and post the outcome.


    Ebenezer

    Wednesday, October 5, 2016 1:44 AM
  • Hi Cole

    Your suggestion didn't work. I am getting

    -  $exception {"LINQ to Entities does not recognize the method 'System.DateTime ParseExact(System.String, System.String, System.IFormatProvider)' method, and this method cannot be translated into a store expression."} System.Exception {System.NotSupportedException}

    I am now Implementing the suggestion from David and will post outcome soon


    Ebenezer

    Wednesday, October 5, 2016 2:12 PM
  • Hi David

    Thanks a lot. Your solution works and made things simpler.


    Ebenezer

    • Marked as answer by denkyira Wednesday, October 5, 2016 5:07 PM
    Wednesday, October 5, 2016 5:07 PM