ElenaDBA ElenaDBA - 3 months ago 26
C# Question

Linq query and returning DateTime field in a short date format

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

Answer

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
});