none
How to convert DateTime to String LINQ to Entities RRS feed

  • Question

  • Is it possible to convert a DateTime to String without .ToString() using Linq to Entities?

    I need to combine two fields into a string (State), and one of them is DateTime (DeliveryDate):

    State = s.Delivered == false ? "Not delivered" : "Delivered on " + s.DeliveryDate,

    regards,

    Nico.


    Wednesday, March 12, 2014 5:31 PM

Answers

  • Yes.  But it's ugly and probably not what you want.

    In LINQ 2 Entities (L2E) when you write a LINQ query it is translated to TSQL and executed on the server.  So you can only use expressions in your query that L2E knows how to translate to TSQL.

    When you execute the query you get an in-memory collection of objects, and you can write further queries using LINQ 2 Objects (L2O) on that.  And in L2O you can execute arbitrary .NET code as part of your queries.

    eg

    var q = from e in db select new { e.Delivered, e.DeliveryDate };

    var q2 = from e in q.AsEnumerable() select new {State = e.Delivered == false?"Not Delivered","Delivered On " + e.DeliveryDate.ToString() };


    Running .AsEnumerable() or .ToList() on the L2E query executes the query and transforms it into an IEnumerable<T>, which you can then use in further queries.

    David


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



    Wednesday, March 12, 2014 5:58 PM

All replies

  • Yes.  But it's ugly and probably not what you want.

    In LINQ 2 Entities (L2E) when you write a LINQ query it is translated to TSQL and executed on the server.  So you can only use expressions in your query that L2E knows how to translate to TSQL.

    When you execute the query you get an in-memory collection of objects, and you can write further queries using LINQ 2 Objects (L2O) on that.  And in L2O you can execute arbitrary .NET code as part of your queries.

    eg

    var q = from e in db select new { e.Delivered, e.DeliveryDate };

    var q2 = from e in q.AsEnumerable() select new {State = e.Delivered == false?"Not Delivered","Delivered On " + e.DeliveryDate.ToString() };


    Running .AsEnumerable() or .ToList() on the L2E query executes the query and transforms it into an IEnumerable<T>, which you can then use in further queries.

    David


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



    Wednesday, March 12, 2014 5:58 PM
  • Thank you David. That's what I found so far and there aren't many alternatives. I'm having trouble adapting it to my code. I just shared a tiny part of a larger function I'm writing to build a WCF RIA Service for a Visual Studio LightSwitch solution. It uses the IQueryable<> interface. Could you point me in the right direction?

    public IQueryable<EnhancedSites> GetSites()
            {
                
                var colEnhancedSite = from s in this.Context.Sites
                                       join e in this.Context.Entities on s.Entity_Site equals e.Id into _sites1
                                       join po in this.Context.PublicOrg on s.PublicOrg_Site equals po.Id into _sites2
                                       from _s1 in _sites1.DefaultIfEmpty()
                                       from _s2 in _sites2.DefaultIfEmpty()
                                       
                                       // Combine results in  
                                       // EnhancedSite class
    
                                       select new EnhancedSite
                                       {
                                           Id = s.Id,
                                           URL = s.URL,
    
                                           //more code goes here
                                           //DDJJState = s.Delivered == false ? "Not delivered" : "Delivered on " + s.DeliveryDate.ToString()                                   
                                           // more code goes here
                                       };
                return colEnhancedSite;
            }
    Thank you for your time.

    Wednesday, March 12, 2014 6:30 PM
  • You would just break that into two queries. Something like:

          var siteQuery       = from s in this.Context.Sites
                                join e in this.Context.Entities on s.Entity_Site equals e.Id into _sites1
                                join po in this.Context.PublicOrg on s.PublicOrg_Site equals po.Id into _sites2
                                from _s1 in _sites1.DefaultIfEmpty()
                                from _s2 in _sites2.DefaultIfEmpty()
                                select s;
    
                                // Combine results in  
                                // EnhancedSite class
          var enhancedSites = from s in siteQuery.AsEnumerable()
                                select new EnhancedSite
                                {
                                  Id = s.Id,
                                  URL = s.URL,
    
                                  //more code goes here
                                  //DDJJState = s.Delivered == false ? "Not delivered" : "Delivered on " + s.DeliveryDate.ToString()                                   
                                  // more code goes here
                                };
          return enhancedSites;

    But you wouldn't be able to do server-side filtering after that.  Also are you sure you need the JOINs in that query.  If your model has relationships among entities, joins are rarely needed.

    You might consider creating views for your "enhandced" entities and just query those in the model.

    David


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

    Wednesday, March 12, 2014 6:51 PM
  • David,

    The joins are intended to be left outer joins, because I need to retrieve some values from _s1 and _s2 (omitted on purpose, I wrote //more code here), the lines is

    BelongsTo = _s2.Name ?? _S1.Name

    If you know a way of simplifying the code, I'm very interested.

    I tried your suggestion, but, because of the above, I'm getting this error: "_s1 (and _s2) does nos exist in the current context". Obviously because they are not included in the select clause in the first query. How would I do that?

    Also, provided I can fix that, I'm getting a cast error on the 2nd query, "Cannot impplicitly convert System.Collections.Generic.IEnumerable<> to System.Linq.IQueryable<>"


    any thoughts?
    Wednesday, March 12, 2014 11:49 PM