Core 3.0 toDataSourceResult Exceptions

[jovaughn]

Using EF Core 3.0 and Telerik Version 2019.3.1023.

Several controls are throwing exceptions from the toDataSourceResult Method on IQueryable Types.

These queries all worked on EF Core 2.2

example 1. Has filtering on the name fields from a kendo autocomplete

The LINQ expression 'Where, Person>>( source: OrderBy, Person>, string>( source: LeftJoin, Person, Nullable, TransparentIdentifier, Person>>( outer: Join>( outer: DbSet, inner: DbSet, outerKeySelector: (u) => u.UserInfoId, innerKeySelector: (u0) => u0.UserInfoId, resultSelector: (u, u0) => new TransparentIdentifier( Outer = u, Inner = u0 )), inner: DbSet, outerKeySelector: (ti) => ti.Outer.PersonId, innerKeySelector: (p) => (Nullable)p.PersonId, resultSelector: (ti, p) => new TransparentIdentifier, Person>( Outer = ti, Inner = p )), keySelector: (ti0) => ti0.Outer.Outer.LastName), predicate: (ti0) => Format( format: "{0}, {1}", arg0: ti0.Outer.Outer.LastName, arg1: ti0.Outer.Outer.FirstName).ToLower().Contains("powell"))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

public JsonResult GetUsers([DataSourceRequest]DataSourceRequest request)

        {

            try

            {

                IQueryable<UserInfoViewModel> model = UserInfoListQuery.Get((SecurityEntities)this.DbContext);

                DataSourceResult result = model.ToDataSourceResult(request);

                return Json(new DataSourceResult { Data = result.Data, Errors = null, Total = result.Total });

            }

            catch (Exception ex)

            {

                 

            }

        }

public static IQueryable<UserInfoViewModel> Get(SecurityEntities ctx, Nullable<bool> showAuthorized, Nullable<bool> showEmployees)

{

users = (from ui in ctx.UserInfo
                         join us in ctx.UserInfoStatistic on ui.UserInfoId equals us.UserInfoId
                         join p in ctx.Person on ui.PersonId equals p.PersonId into people
                         from person in people.DefaultIfEmpty()
                         orderby ui.LastName
                         select new UserInfoViewModel()
                         {                     
                             FirstName = ui.FirstName,
                             LastName = ui.LastName,                           
                         });

return users;


}

 

Example 2. has kendo grid inital sorting on a DateTime that was converted to a string

The LINQ expression 'OrderByDescending( source: Where( source: DbSet, predicate: (a) => a.UserInfoId == (Unhandled parameter: __userInfoId_0)), keySelector: (a) => a.RecordModifiedDateTime.ToString("g"))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

public static IQueryable<AuthenticationLogViewModel> GetAuthenticationLogForUser(SecurityEntities ctx, int userInfoId)

        {

 return (from al in ctx.AuthenticationLog

                    where al.UserInfoId == userInfoId

                    orderby al.RecordModifiedDateTime descending

                    select new AuthenticationLogViewModel()

                    {                      

                        ModifiedDate = al.RecordModifiedDateTime.ToString("g"),

                    }

}

 

public JsonResult GetAuthenticationLogForUser([DataSourceRequest]DataSourceRequest request, int userInfoId)
        {
            try
            {
                IQueryable<AuthenticationLogViewModel> model = AuthenticationLogQuery.GetAuthenticationLogForUser((SecurityEntities)this.DbContext, userInfoId);
                DataSourceResult result = model.ToDataSourceResult(request);
                return Json(new DataSourceResult { Data = result.Data, Errors = false, Total = result.Total });
            }
            catch (Exception ex)
            {              
            }
        }

 

 

[Ianko]

Hello Jovaughn,

I created a sample app using .NET Core 3.0 with EF, but I was unable to reproduce the problems described. You can find it attached. 

One possible reason for possible problem I can think of is if the Json Serializer is not set up correctly in the Startup.cs file. 

Regards,
Ianko
Progress Telerik

[jovaughn]

Hey Lanko,

 

I figured out these issues. 

<schema>

            <model>

                <fields>

         


                    <field name="ModifiedDateTime" type="date" />

                </fields>

            </model>

        </schema>

[jovaughn]

Apparently EF 3.0 does not like server side sorting by client side variables. So you either have to sort by a server side field or do a toList() before the DataSourceRequest filtering.

public string ModifiedDate => public DateTime ModifiedDateTime, then define the field in the model schema and format the column.

  <column title="Modified On" field="ModifiedDateTime" format="{0:ddd MM/dd/yy}" width="150" />

[Ianko]

Hi Jovaughn,

Thank you for the input. I will forward this information to the team so that we are aware of this change in EF 3.0. 

Regards,
Ianko
Progress Telerik