Click here to Skip to main content
15,900,725 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi. I'm implementing asp.net core 3.1. I have a column in SQLServer called ApiRequestDate which is of type nvarchar and in my model is of type string. The data is stored in that column in the format yyyy/mm/dd. Now I want to group by year and the month in order to find some related data for that time. For doing so, I wrote query like the following:

var total = from t1 in _context.Apiapp
                        let tg = new
                        {
                            t1.ApiRequestDate.Value.Year,
                            t1.ApiRequestDate.Value.Month
                        }
                        group tg by new { t1.ApiRequestDate.Value.Year, t1.ApiRequestDate.Value.Month }  into newGroup
                        Select new
                        {
                            Month = newGroup.Select(n => n.ApiRequestDate.Value.Month).First(),
                            ReqPerMonthCount = newGroup.Count()
                        };

            var totalCount = total.ToList();


But it doesn't work and it has errors. One of the errors is 'Value" is unknown. And the other on is, the name Select does not exist in the current context.
I appreciate if anyone can suggest me a solution for it.

What I have tried:

var total = from t1 in _context.Apiapp
let tg = new
{
t1.ApiRequestDate.Value.Year,
t1.ApiRequestDate.Value.Month
}
group tg by new { t1.ApiRequestDate.Value.Year, t1.ApiRequestDate.Value.Month } into newGroup
Select new
{
Month = newGroup.Select(n => n.ApiRequestDate.Value.Month).First(),
ReqPerMonthCount = newGroup.Count()
};

var totalCount = total.ToList();
Posted
Updated 1-May-20 5:47am
v2
Comments
F-ES Sitecore 1-May-20 10:33am    
".Value" only applies to nullable types, so chances are that ApiRequestDate is a normal string, not a nullable one so you reference it direct. However it won't have a Year property as it isn't a date, it is a string. I'm not sure how much conversion from string to dates you can do in a linq query, but by far the best solution is posted as #1 - if you column holds a date then make it a date type and many of your problems go away and the performance increases too.

This is exactly why you should user proper datatype in your database in the first place. If you setup your column with its proper DateTime datatype, and use matching .NET type, then your query becomes trivial because you can take advantage of all properties, methods and operators on DateTime type.

As a general rule: the only place you need a string to represent a datetime value, is when you want to present the value to the end user. Everything before this step should not deal with any string, at all.
 
Share this answer
 
Comments
MadMyche 1-May-20 10:47am    
+5 We have a winner.
The problem is obvious; and is exactly as phil.o stated: incorrect data typing in both your database and in your model.
The only valid fix for this is to correct this.

Now, in the meantime; you could apply some bandages to this. My first thought would be to add another property to your model which is defined as a get of the parsed DateTime value parsed of your existing ApiRequestDate property

DateTime.ParseExact Method (System) | Microsoft Docs[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900