Linq query and returning DateTime field in a short date format

Question

I have two tables in the database Order and OrderDetails. I am joing them in a Linq query but only pulling data from Orders table. I also have a corresponding class - ABOrders:

[Table("[Order]")]
public partial class ABOrder
    {
        [Key]
        public int OrderID { get; set; }
        public string Field1 { get; set; }
        public string Field2 { get; set; }
        public DateTime OrderDate { get; set; }
    }

The issue is that OrderDate comes with date and time and I just need it as a string in ShortDate format. Originally I have tried:

var result = (from a in ctx.Order
                         join b in ctx.OrderDetails on a.OrderId Equals b.OrderId
                         where a.Field1.Equals(id) && b.Field1.Contains("TEST")
                         select new myClass.ABOrder
                         {
                             OrderId = a.OrderId,
                             Field1 = a.Field1,
                             Field2=a.Field2, 
                             OrderDate = String.Format("MM/dd/yyyy", a.OrderDate)
                         };

but that errored out, so I created a new class:

[Table("[Order]")]
public partial class CDOrder
    {
        [Key]
        public int OrderID { get; set; }
        public string Field1 { get; set; }
        public string Field2 { get; set; }
        public string OrderDate { get; set; }
    }

and tried the following Linq query:

var result = (from a in ctx.Order
                         join b in ctx.OrderDetails on a.OrderId Equals b.OrderId
                         where a.Field1.Equals(id) && b.Field1.Contains("TEST")
                         select new myClass.ABOrder
                         {
                             OrderId = a.OrderId,
                             Field1 = a.Field1,
                             Field2=a.Field2, 
                             OrderDate = a.OrderDate
                         }).AsEnumerable().Select(x => new myClass.CDOrder
                         {
                             OrderId = x.OrderId,
                             Field1 = x.Field1,
                             Field2=x.Field2, 
                             OrderDate = String.Format("MM/dd/yyyy", x.OrderDate)                 
                         });

And now I am getting error:

The entity types 'ABOrder' and 'CDOrder' cannot share table 'Order' because they are not in the same type hierarchy or do not have a valid one to one foreign key relationship with matching primary keys between them.

How can I accomplish what i want - retrieve all data from Order table but with data in short format?

P.S. I cannot make any adjustments when displaying data to the user, so i have to do that in my query

you are getting back a datetime object. Just call .ToString("MM/dd/yyyy")when you actually want to display them to the user — Steve, Aug 12 '16 at 21:09
I tried to do that originally, but that's the thing, I cannot make any adjustments when displaying it to the user — Coding Duchess, Aug 12 '16 at 21:15
Wouldn't just removing the attribute `[Table("[Order]")]` from the class `CDOrder` solve the problem in the above case or does it need to be recognised as part of the DB for that query to work? — starlight54, Aug 12 '16 at 21:19
I tried that and got a new error: The entity or complex type 'myCLass.DAL.ABOrder' cannot be constructed in a LINQ to Entities query — Coding Duchess, Aug 15 '16 at 12:49

score 3 · Answer 1 · answered Aug 13 '16 at 07:59

Solution no.1 - get from database exactly what you want:

var result=ctx.Order.Where(a=>a.Field1.Equals(id))
    .Join(ctx.OrderDetails.Where(b=>b.Field1.Contains("TEST")),
     x=>x.OrderId ,y=>y.OrderId ,(x,y)=>new CDOrder
    {
     OrderId = x.OrderId,
     Field1 = x.Field1,
     Field2=x.Field2, 
     OrderDate = x.OrderDate.Month+"/"+x.OrderDate.Day+"/"+x.OrderDate.Year
    }).ToList();

Solution no.2 - get from database List of Orders and perform in memory the Select statment:

var result=ctx.Order.Where(a=>a.Field1.Equals(id))
    .Join(ctx.OrderDetails.Where(b=>b.Field1.Contains("TEST")),
     x=>x.OrderId ,y=>y.OrderId ,(x,y)=>x)
.AsEnumerable()
.Select(x=>new CDOrder
    {
     OrderId = x.OrderId,
     Field1 = x.Field1,
     Field2=x.Field2, 
     OrderDate = x.OrderDate.ToString("MM/dd/yyyy")
    }).ToList();

In first query I build the string date, because LINQ to Entities does not recognize the method .ToString().

In the second query I used .AsEnumerable() to get a List<Order>, then I performed in memory the select statment.

your solution gives me an error `The entity or complex type 'myCLass.DAL.CDOrder‌' cannot be constructed in a LINQ to Entities query` — Coding Duchess, Aug 15 '16 at 13:32
@ElenaDBA, try removing `CDOrder` from query, and just return an Anonymus type, is it posible the error to come from the fact that your class is a partial, let me know if you get any error! — Lucian Bumb, Aug 15 '16 at 18:02
Mate can you please help me with this question:http://stackoverflow.com/questions/39275597/how-to-give-custom-implementation-of-updateasync-method-of-asp-net-identity — I Love Stackoverflow, Sep 01 '16 at 15:33

score 1 · Answer 2 · answered Aug 12 '16 at 21:17

In the backend a DateTime is a DateTime, its not a string. So it makes no sense of trying to make the DB to return a ShortDate. Instead you can make another read only field

something like

public string OrderDateDate
{
    get
    {
        return OrderDate.ToString("MM/dd/yyyy");
    }
}

score 0 · Accepted Answer · edited Sep 17 '19 at 20:38

Solved it by switching to anonymous type. Upvoted helpful answers and comments: starlight54 - removing table name alias form CDOrder, and Lucian Bumb for suggesting a.OrderDate.Month + "/" + a.OrderDate.Day + "/" + a.OrderDate.Year

Below is what worked:

var result = (from a in ctx.Orders
                         join b in ctx.OrderDetails on a.OrderId Equals b.OrderId
                         where a.Field1.Equals(id) && b.Field1.Contains("TEST")
                         select new 
                         {
                             OrderId = a.OrderId,
                             Field1 = a.Field1,
                             Field2=a.Field2, 
                             OrderDate = a.OrderDate.Month + "/" + a.OrderDate.Day + "/" + a.OrderDate.Year
                         }).AsEnumerable().Select(x => new myClass.CDOrder
                         {
                             OrderId = x.OrderId,
                             Field1 = x.Field1,
                             Field2=x.Field2, 
                             OrderDate = x.OrderDate             
                         });

Linq query and returning DateTime field in a short date format

3 Answers3