How to read data from a nested select query linq

Question

I have a query like the following which is of type linq.

var querymiangin = (from t1 in _context.Apiapplicant
                    join t2 in _context.ApiApplicantHistory on t1.Id equals t2.ApiApplicantId
                    join t3 in _context.EntityType on t2.LastReqStatus equals t3.Id
                    where t1.IsDeleted == false && t1.LastRequestStatus == t2.Id && t3.Name == "granted"
                    select new { A = t1, B = t2, Year = t1.ApiRequestDate.Substring(0, 4), Month = t1.ApiRequestDate.Substring(5, 2) } into joined
                    group joined by new { joined.Year, joined.Month, joined.B.LastReqStatus } into grouped
                    select grouped.Select(g => new { ApiReqDate = g.A.ApiRequestDate, ApiDate = g.B.Date, ApiLastReqStatus = g.B.LastReqStatus, ApiYear = g.Year, ApiMonth = g.Month })).ToList();

In the select part, ApiReqDate and ApiDate has multiple records. Now my problem is for each group of month and year, I have multiple ApiDate and ApiReqDate records and I want for each group based on a condition (t1.LastRequestStatus == t2.Id && t3.Name == "granted") by using GetPersianDaysDiffDate() method, obtain the difference between ApiReqDate and its related ApiDate records for each month and then find their average in that month.

For doing that, I have written code like this:

var avgDateDiff = querymiangin.DefaultIfEmpty()
       .GroupBy(x => new { x.ApiYear, x.ApiMonth }, (key, g) => new
       {
           key.ApiYear,
           key.ApiYear,
           Avg = g.Average(y => GetPersianDaysDiffDate(y.ApiReqDate,y.ApiDate))
       })
       .ToList();

But the problem is each parameter x.ApiYear, x.ApiMonth,y.ApiReqDate,y.ApiDate are unknown and it shows me error. I appreciate if anyone can suggest me a solution for that.

Mohammed Sajid · Accepted Answer · 2020-06-21 08:49:15Z

1

1 - For the first request querymiangin, you don't need to group by statement, change little the code to :

var querymiangin = (from t1 in Apiapplicant
                    join t2 in ApiApplicantHistory on t1.Id equals t2.ApiApplicantId
                    join t3 in EntityType on t2.LastReqStatus equals t3.Id
                    where t1.IsDeleted == false && t1.LastRequestStatus == t2.Id && t3.Name == "granted"
                    select new
                    {
                        ApiReqDate = t1.ApiRequestDate,
                        ApiDate = t2.Date,
                        ApiYear = t1.ApiRequestDate.Substring(0, 4),
                        ApiMonth = t1.ApiRequestDate.Substring(5, 2)
                    }).ToList();

2 - For the second query avgDateDiff, use GroupBy by ApiYear and ApiMonth and calculate the Average, like :

var avgDateDiff = querymiangin
       .GroupBy(x => new { x.ApiYear, x.ApiMonth }, (key, g) => new
       {
           key.ApiYear,
           key.ApiMonth,
           Avg = g.Average(y => GetPersianDaysDiffDate(y.ApiReqDate, y.ApiDate))
       }).ToList();

I hope you find this helpful.

edited Jun 21, 2020 at 8:49

answered Jun 20, 2020 at 8:23

Mohammed Sajid

4,9132 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

MinaMRM Over a year ago

Thank you for your suggestion. I edited my code like what you mentioned however the problem still exists and the parameters are unknown.

MinaMRM Over a year ago

Thank you again for your help. for your first query suggestion, there is an error like the following: An unhandled exception occurred while processing the request. InvalidOperationException: Processing of the LINQ expression 'x => x' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

MinaMRM Over a year ago

And for your second suggestion, there is an error like the following: InvalidOperationException: The LINQ expression '(GroupByShaperExpression: KeySelector: new { Year = (SUBSTRING((a.apiRequestDate), ((0) + (1)), (4))), Month = (SUBSTRING((a.apiRequestDate), ((5) + (1)), (2))), LastReqStatus = (a.lastReqStatus) }, ElementSelector:new { A = (EntityShaperExpression: EntityType: Apiapplicant ValueBufferExpression: (ProjectionBindingExpression: A) ....

MinaMRM Over a year ago

Thank you for your reply. What should I try?

MinaMRM Over a year ago

There is an error for SelectMany part: The type arguments with the method IEnumerable.SelectMany can not be inferred from the usage.

|

Collectives™ on Stack Overflow

How to read data from a nested select query linq

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related