'Query group by and convert date in C# Entity Framework?

// GET api/PdoGreige/ReportByBuffer
[HttpGet, ActionName("Buffer")]
public async Task<HttpResponseMessage>Get_Buffer()
{     
    var result = (from t1 in db.INVN_PROD_MOVEMENT_LOG
                  join t2 in db.INVN_PROD_PDO_ITEM on t1.PDO_ITEM_ID equals t2.PDO_ITEM_ID
                  join t4 in db.INVN_PROD_PDO_GREIGE on t2.PDO_ITEM_ID equals t4.PDO_ITEM_ID
                  join t3 in db.INVN_PROD_RM_GREIGE on t4.GREIGE_ID equals t3.GREIGE_ID

                  //where t3.GREIGE_ID != null
                  //where t3.WEIGHT_REMAIN != null
                  //where t3.ITEM_NAME != null
                
                  where t1.WORKCENTER == "O.DRY"
                  orderby t1.UPDATE_TIME descending
                  
                  select new
                  {
                      UPDATE_TIME = t1 == null ? DateTime.Now : t1.UPDATE_TIME,
                      t3.GREIGE_ID,
                      t3.ITEM_NAME,
                      t3.WEIGHT_REMAIN,
                      t3.COMMENTS,
                      t1.WORKCENTER,
                  }).Distinct();

    return Request.CreateResponse(
        HttpStatusCode.OK,
        await result.ToListAsync(),
        Configuration.Formatters.JsonFormatter
    );
}

This is my C# Query. How if I want to convert format date to ("dd/MM/yyy") in C# and grouping it. Like my query in SQL Server I tried a lot of syntax but didn't work. Help me to resolve it

c++sharpsqlsql-servervisual-studioasp.net-core


Solution 1:[1]

I think it's not so much about date format 105 (which isn't dd/MM/yyyy it's dd-MM-yyyy) but that it strips off any time aspect so the grouping is to whole day level

In c# an equivalent would be

UPDATE_TIME = t1 == null ? DateTime.Today : t1.UPDATE_TIME.Date

which "removes" the time aspect (stops it from subdividing the grouping), by setting it to midnight but more usefully keeps the date as a date rather than putting it to string; it can be formatted to string in the user's culture closer to the UI

If you really do want it as a string:

UPDATE_TIME = (t1 == null ? DateTime.Now : t1.UPDATE_TIME).ToString("dd/MM/yyyy")

(Or as 105, use hyphens instead of slashes)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Caius Jard

Related Questions