Grouping with complex ViewModel generates wrong sql command

[NoobMaster]

Hello

im trying to apply grouping

Here is my Read method

01.var result = await _helpDeskDbContext.Set<Ticket>()
02.               .Where(a => a.IsDeleted == false && AllowedCats.Contains(a.CategoryIdRef.Value))
03.               .OrderBy(a => a.Status.Sort)
04.               .Select(a => new TicketViewModel
05.           {
06.               Assignee = a.AssigneeUser.FullName,
07.               Category = a.Category.Name,
08.               Code = a.Code,
09.               Project = a.Project.Name,
10.               Id = a.Id,
11.               IsEditable = a.Status.IsEditable,
12.               Priority = a.Priority.Name,
13.               Requester = a.Requester.Name,
14.               RequestedOn = a.CreatedDate.ToString("yyyy/MM/dd hh:mm tt", System.Globalization.CultureInfo.InvariantCulture),
15.               Status = a.Status.Name,
16.               Subject = a.Subject,
17.               StatusCode = a.Status.Code,
18.               PriorityColor = new DataItem { text = a.Priority.ForeColor, value = a.Priority.BackColor },
19.               StatusColor = new DataItem { text = a.Status.ForeColor, value = a.Status.BackColor },
20.               ProjectColor = new DataItem { text = a.Project.ForeColor, value = a.Project.BackColor },
21.               StatusSort = a.Status.Sort
22.           }).ToDataSourceResultAsync(request);

 

and here is my grid configuration

.DataSource(op =>
                                       {
                                           op.Ajax().Model(m => m.Id(x => x.Id)).Group(a => a.Add(s => s.Assignee)).Read(r => r.Action("Read", "Tickets"));
                                       })

 

and the sql command from kestrel 

SELECT [t].[Assignee], [t].[Category], [t].[Code0], [t].[Project], [t].[Id], [t].[IsEditable], [t].[Priority], [t].[Requester], [t].[CreatedDate], [t].[c], [t].[Status], [t].[Subject], [t].[StatusCode], [t].[text], [t].[value], [t].[text0], [t].[value0], [t].[text1], [t].[value1], [t].[StatusSort], [t].[FullName], [t].[Name], [t].[Code], [t].[Name], [t].[IsEditable], [t].[Name], [t].[Name], [t].[Name], [t].[Code], [t].[ForeColor], [t].[BackColor], [t].[ForeColor], [t].[BackColor], [t].[ForeColor], [t].[BackColor], [t].[Sort]
      FROM (
          SELECT [a.AssigneeUser].[FullName] AS [Assignee], [a.Category].[Name] AS [Category], [a].[Code] AS [Code0], [a.Project].[Name] AS [Project], [a].[Id], [a.Status].[IsEditable], [a.Priority].[Name] AS [Priority], [a.Requester].[Name] AS [Requester], [a].[CreatedDate], N'yyyy/MM/dd hh:mm tt' AS [c], [a.Status].[Name] AS [Status], [a].[Subject], [a.Status].[Code] AS [StatusCode], [a.Priority].[ForeColor] AS [text], [a.Priority].[BackColor] AS [value], [a.Status].[ForeColor] AS [text0], [a.Status].[BackColor] AS [value0], [a.Project].[ForeColor] AS [text1], [a.Project].[BackColor] AS [value1], [a.Status].[Sort] AS [StatusSort]
          FROM [HelpDesk].[Tickets] AS [a]
          LEFT JOIN [HelpDesk].[Contacts] AS [a.Requester] ON [a].[RequesterIdRef] = [a.Requester].[Id]
          LEFT JOIN [HelpDesk].[Priorities] AS [a.Priority] ON [a].[PriorityIdRef] = [a.Priority].[Id]
          LEFT JOIN [HelpDesk].[Projects] AS [a.Project] ON [a].[ProjectIdRef] = [a.Project].[Id]
          LEFT JOIN [HelpDesk].[Categories] AS [a.Category] ON [a].[CategoryIdRef] = [a.Category].[Id]
          LEFT JOIN [HelpDesk].[Status] AS [a.Status] ON [a].[StatusIdRef] = [a.Status].[Id]
          LEFT JOIN [dbo].[UserInfos] AS [a.AssigneeUser] ON [a].[AssigneeIdRef] = [a.AssigneeUser].[UserId]
          WHERE ([a].[IsDeleted] = 0) AND COALESCE([a].[CategoryIdRef], 0) IN (3)
          ORDER BY [a.AssigneeUser].[FullName], [a.Status].[Sort]
          OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
      ) AS [t]
      ORDER BY [t].[FullName]

 

in the read method gives error 500 and the i looked to my error logs and i found below error 

 

System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'FullName'.
Invalid column name 'Name'.
Invalid column name 'Code'.
Invalid column name 'Name'.
Invalid column name 'Name'.
Invalid column name 'Name'.
Invalid column name 'Name'.
Invalid column name 'Code'.
Invalid column name 'ForeColor'.
Invalid column name 'BackColor'.
Invalid column name 'ForeColor'.
Invalid column name 'BackColor'.
Invalid column name 'ForeColor'.
Invalid column name 'BackColor'.
Invalid column name 'Sort'.
Invalid column name 'FullName'.

 

please i need help.

am i doing something wrong ? or its in Telerik side

Thanx

Ahmed

[NoobMaster]

please i need help

[NoobMaster]

Is Telerik Alive !?

[Viktor Tachev]

Hi Ahmed,

 

I apologize for the late reply.

 

The error is likely thrown because of the way data is mapped. Would you map the items using the ToDataSourceResult method like illustrated in the example below?

https://docs.telerik.com/aspnet-mvc/helpers/data-management/grid/binding/ajax-binding#using-the-view-models

 

Give the approach a try and let me know how it works for you.

 

Regards,
Viktor Tachev
Progress Telerik

[NoobMaster]

Hi

thanx for your response

i have tried your solution ( given link )

as below

var result = await _helpDeskDbContext.Set<Ticket>()
              .Where(a => a.IsDeleted == false && AllowedCats.Contains(a.CategoryIdRef.Value))
              .OrderBy(a => a.Status.Sort)
              .ToDataSourceResultAsync(request, a => new TicketViewModel
              {
                  Assignee = a.AssigneeUser.FullName,
                  Category = a.Category.Name,
                  Code = a.Code,
                  Project = a.Project.Name,
                  Id = a.Id,
                  IsEditable = a.Status.IsEditable,
                  Priority = a.Priority.Name,
                  Requester = a.Requester.Name,
                  RequestedOn = a.CreatedDate.ToString("yyyy/MM/dd hh:mm tt", System.Globalization.CultureInfo.InvariantCulture),
                  Status = a.Status.Name,
                  Subject = a.Subject,
                  StatusCode = a.Status.Code,
                  PriorityColor = new DataItem { text = a.Priority.ForeColor, value = a.Priority.BackColor },
                  StatusColor = new DataItem { text = a.Status.ForeColor, value = a.Status.BackColor },
                  ProjectColor = new DataItem { text = a.Project.ForeColor, value = a.Project.BackColor },
                  StatusSort = a.Status.Sort
              });

 

and now im getting ( 

Invalid property or field - 'Assignee' for type: Ticket
   at Kendo.Mvc.Infrastructure.Implementation.Expressions.MemberAccessTokenExtensions.CreateMemberAccessExpression(IMemberAccessToken token, Expression instance)

)

error.

any thoughts ?

 

Regards,

Ahmed

[NoobMaster]

please i'm waiting for your response , ( 3 days no replay )

[Viktor Tachev]

Hello Ahmed,

 

I tried to replicate the behavior in a sample project, however, I was not able to. Give the attached sample a try and let me know how it works for you. It would be great if you can modify the sample in a way that the error is replicated and send it back. This will enable us to examine the behavior locally and look for what is causing it.

 

Regards,
Viktor Tachev
Progress Telerik

[NoobMaster]

Hi Viktor

Thanks for your response.

please the attached sample that you have provided is not same as my scenario ( a complex view model ).

you sample project is very simple , and if i set grouping property to one of the main table properties works, but when i include other tables with join and set a property in other table as grouping field then gives me error that says the Assignee field ( the grouping field ) is invalid for type main table which is ( Ticket table ).

please ToDataSourceResult with selector method generates wrong sql select command, this is the whole problem

 

Regards

Ahmed :)

[NoobMaster]

Hi Viktor

i have attached your sample with my modification and still the error appears in the provided sample

here is the download link for the sample project : WebApplication1.zip ( i could not upload the project in this post due to your Upload file extension restriction, it only allows photos to be uploaded )

Regard

Ahmed :)

[Viktor Tachev]

Hello Ahmed,

 

Thank you for the sample project.

In this scenario the error is thrown because the grouping is performed on the Product type. However, there is no CreatedByUserName field in Product and that results in an error. In order to resolve this I suggest mapping the data to ProductViewModel before calling ToDataSourceResult.

 

public ActionResult Read([DataSourceRequest] DataSourceRequest request)
{
	var result = products.Select(p => new ProductViewModel
	{
		ProductID = p.ProductID,
		ProductName = p.ProductName,
		UnitsInStock = p.UnitsInStock,
		CreatedByUserName = p.User.UserName
	});

	return Json(result.ToDataSourceResult(request));
}

 

After this modification the Grid was displayed as expected on my end. I am also attaching the updated sample as reference. Give it a try and let me know how it works for you.

 

Regards,
Viktor Tachev
Progress Telerik

[NoobMaster]

Hi :)

Thanx for your replay and sorry for my late replay.

i have applied your suggestion ( mapped to the view model and after that applied ToDataSourceResultAsync method )

to my real project, but still through sql server error ( the sample app is working perfectly ).

the error is 

System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'AssigneeIdRef'.
Invalid column name 'FullName'.
Invalid column name 'Name'.
Invalid column name 'ProjectIdRef'.........

 

this error is thrown due to wrong sql select command generation

here is the generated sql command with your applied solution

SELECT [t].[Subject], [t].[Code], [t].[Id], [t].[CreatedDate], [t].[c], [t].[Assignee], [t].[Category], [t].[Project], [t].[IsEditable], [t].[Priority], [t].[Requester], [t].[Status], [t].[StatusCode], [t].[c0], [t].[text], [t].[value], [t].[c1], [t].[text0], [t].[value0], [t].[c2], [t].[text1], [t].[value1], [t].[StatusSort], [t].[AssigneeIdRef], [t].[FullName], [t].[Name], [t].[ProjectIdRef], [t].[Name], [t].[StatusIdRef], [t].[IsEditable], [t].[PriorityIdRef], [t].[Name], [t].[RequesterIdRef], [t].[Name], [t].[Name], [t].[Code], [t].[ForeColor], [t].[BackColor], [t].[ForeColor], [t].[BackColor], [t].[ForeColor], [t].[BackColor], [t].[Sort]
      FROM (
          SELECT [a].[Subject], [a].[Code], [a].[Id], [a].[CreatedDate], N'yyyy/MM/dd hh:mm tt' AS [c], CASE
              WHEN [a].[AssigneeIdRef] IS NOT NULL
              THEN [a.AssigneeUser].[FullName] ELSE N''
          END AS [Assignee], [a.Category].[Name] AS [Category], CASE
              WHEN [a].[ProjectIdRef] IS NOT NULL
              THEN [a.Project].[Name] ELSE N''
          END AS [Project], CASE
              WHEN [a].[StatusIdRef] IS NOT NULL
              THEN [a.Status].[IsEditable] ELSE CAST(0 AS BIT)
          END AS [IsEditable], CASE
              WHEN [a].[PriorityIdRef] IS NOT NULL
              THEN [a.Priority].[Name] ELSE N''
          END AS [Priority], CASE
              WHEN [a].[RequesterIdRef] IS NOT NULL
              THEN [a.Requester].[Name] ELSE N''
          END AS [Requester], CASE
              WHEN [a].[StatusIdRef] IS NOT NULL
              THEN [a.Status].[Name] ELSE N''
          END AS [Status], CASE
              WHEN [a].[StatusIdRef] IS NOT NULL
              THEN [a.Status].[Code] ELSE N''
          END AS [StatusCode], CASE
              WHEN [a].[PriorityIdRef] IS NOT NULL
              THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
          END AS [c0], [a.Priority].[ForeColor] AS [text], [a.Priority].[BackColor] AS [value], CASE
              WHEN [a].[StatusIdRef] IS NOT NULL
              THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
          END AS [c1], [a.Status].[ForeColor] AS [text0], [a.Status].[BackColor] AS [value0], CASE
              WHEN [a].[ProjectIdRef] IS NOT NULL
              THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
          END AS [c2], [a.Project].[ForeColor] AS [text1], [a.Project].[BackColor] AS [value1], CASE
              WHEN [a].[StatusIdRef] IS NOT NULL
              THEN [a.Status].[Sort] ELSE CAST(0 AS tinyint)
          END AS [StatusSort]
          FROM [HelpDesk].[Tickets] AS [a]
          LEFT JOIN [HelpDesk].[Contacts] AS [a.Requester] ON [a].[RequesterIdRef] = [a.Requester].[Id]
          LEFT JOIN [HelpDesk].[Priorities] AS [a.Priority] ON [a].[PriorityIdRef] = [a.Priority].[Id]
          LEFT JOIN [HelpDesk].[Projects] AS [a.Project] ON [a].[ProjectIdRef] = [a.Project].[Id]
          LEFT JOIN [HelpDesk].[Categories] AS [a.Category] ON [a].[CategoryIdRef] = [a.Category].[Id]
          LEFT JOIN [dbo].[UserInfos] AS [a.AssigneeUser] ON [a].[AssigneeIdRef] = [a.AssigneeUser].[UserId]
          LEFT JOIN [HelpDesk].[Status] AS [a.Status] ON [a].[StatusIdRef] = [a.Status].[Id]
          WHERE 0 = 1
          ORDER BY [Assignee], [a.Status].[Sort]
          OFFSET @__p_2 ROWS FETCH NEXT @__p_3 ROWS ONLY
      ) AS [t]
      ORDER BY CASE
          WHEN [t].[AssigneeIdRef] IS NOT NULL
          THEN [t].[FullName] ELSE N''
      END

 

Thanx in advance

i appreciate your help :)

 

Ahmed

[Viktor Tachev]

Hi Ahmed,

 

Would you send us a runnable sample with the error or modify the sample from my previous post in a way that the error is replicated? This will enable us to examine it and look for its cause.

 

Regards,
Viktor Tachev
Progress Telerik

[NoobMaster]

Hi Viktor

Thanks for your response

here is the runnable Sample with the error ( WebApplication1.zip )

i appreciate your help :)

 

Regards

Ahmed

 

[Viktor Tachev]

Hello Ahmed,

 

Thank you for the runnable project.

In order to avoid the error I suggest using the Include method and passing the User table to it. The updated Read ActionMethod would look like this:

 

public ActionResult Read([DataSourceRequest] DataSourceRequest request)
{
	var result = _applicationDbContext.Products.Include("User").Select(p => new ProductViewModel
	{
		ProductID = p.ProductID,
		ProductName = p.ProductName,
		UnitsInStock = p.UnitsInStock,
		CreatedByUserName = p.User.UserName
	});

	return Json(result.ToDataSourceResult(request));

}

 

The method would require the Microsoft.EntityFrameworkCore namespace:

using Microsoft.EntityFrameworkCore;

 

I am also sending the updated project as reference. Give it a try and let me know how it works for you.

 

Regards,
Viktor Tachev
Progress Telerik

[NoobMaster]

Hi dude

Thanks for your response.

Finally Your solution worked  and i really appreciate your help :)

thanx again :) 

 

Regards

Ahmed