fingers10 / jquerydatatablesserverside Goto Github PK

Hi,
I'm trying to implement your code over a database example. I've made minimum changes to the example to instead point to a db table.
When I debug I get an error with the GetDataAsync function, Visual Studio is reporting the error as...
"Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement"

I'm trying against a MS SQL database.

Can we get date range filter facility on date column?

How could I style the length menu? I was searching your demo and didn't see an example of this.

Hi, thanks again for this.
With regards specifying the model to be passed to the TagHelper, can I specify a model directly or must I only use the model specified for the page, i.e. @model?
E.g can I specify model="MyDataViewModel" ?

Many thanks.

Hi Abdul Rahman,

I know this problem is not related to your component but i wanted to ask some help from you.
I am using mongo db as a database, everything is going fine but i am not able to run search functionality successfully.
i am having Value cannot be null. (Parameter 'itemName') error. after googling a bit i came across this link. I provided the name as mentioned in the blog and the error is gone but i am not getting the search functionality properly.
can you please help me regarding problem.

I'm don't want to have a search for each field. How do I use the Language => Search INPUT as my primary mode of searching through column data?

When I debug, clearly data is passed through the parameters, however no data is binded to the return model.

Add the ability to upload full data for excel. Excluding pagination.

I propose to implement this with a separate button "Export all to Excel".
When you click on this button, the Length field of the JqueryDataTablesParameters class is set to -1. This would be tantamount to if we chose "Show all entries" - then, as the developers of datables intended, an example. But I suggest adding the ability to upload all the data without displaying them.

how can we use localization? Please show an example

Querying a table with few entries is also taking much time?.

DataTables.net supports complex headers, but must have the headers hard-coded into the html, not initialized by the javascript.

Can you please write a solution for the tag helper to include any complex header groups?

https://datatables.net/extensions/fixedcolumns/examples/styling/rowspan.html for more details

Built-in DataTables search does send correct JSON to server with non-empty search property, but it seems the library simply ignores it. Since search itself is provided, I see this as missed opportunity rather than bug.

Your example provides external search facilities instead, but that requires developer interference in JS search handler or server-side controller/API endpoint. Having that done by library itself will be appreciated.

To reproduce:

1. Use Demo;
2. Modify wwwroot/js/app.js: add f option somewhere into 'dom';
3. Observe that built-in search triggers loading data popup but does nothing.

SearchPanes extension since 1.1 adds server-side processing support.

Such feature would be a great addition to this library.

95% of time,we need sort operations over different entities in different tables.

Current SortOptionsProcessor's biggest limitation in my opinion is sort can only be applied to single entity.

Example api
new SortOptionsProcessor().Apply(query,table);

DemoModel is the final select/projection of query.

I was thinking you could add overload to SortOptionsProcessor. Also I could submit PR for this ,If I have some free time available.

Can we use this in Blazor Server Side app?

I offer the following types:

1. input type date or datetime-local
2. input type number
3. input type string
4. select
5. multiple select

Thanks for an amazing piece of genius code. Not sure if anyone else is experiencing the following issue:

After updating from V2.1.0 to V2.2.0 the search input boxes are no longer created dynamically after the table is initiated.

Hi,

I am trying to implement this extension in my project, but it seems that individual column search doesn't work with complex objects (at least in my case?).

I have an table 'Orders' in database. Order entity embraces fields like Id, Status, Customer.Firstname, Customer.Lastname, Customer.Email where the last three belong to class Customer which has One-To-Many relationship with Order entity. And while search by, for instance, Id works just fine, any attempt to sort/filter by some of last 3 columns fails at this part:
query = new SearchOptionsProcessor<OrderObject, Order>().Apply(query, table.Columns);

Is that actually not supported yet or it's just some problem with mapping on my side?

Thank you beforehand :)

Describe the question
A clear and concise description of what the question is.
I am not getting proper soluting in .net core 3.1 for onclick data fetch

To Produce
Steps to produce the behavior:

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. How this can be done?

Expected behavior
A clear and concise description of what you expect to happen along with your view model and domain model. Please note, questions without view model and domain model will be closed without any further discussion. Please check the example project before posting. Most of the times, you would find the answer in example solution.

Screenshots
If applicable, add screenshots to help explain your question.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Smartphone (please complete the following information):

• Device: [e.g. iPhone6]
• OS: [e.g. iOS8.1]
• Browser [e.g. stock browser, safari]
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

Hi Abdul Rahman,
Great job its really a nice article. I am using jquery datatable in my project but the problem is i dont want to show some of my model fields in my grid how can i achieve this. i tried using the visible property of grid column but did't work.

secondly my param for LoadData is always null and i am not able to solve this problem can you help me regarding this? for this i have made some tests and i came to know that it is null because the "Order" property of "JqueryDataTablesParameters" is not binding to the posted data. i did this by creating my own test paramerter class and included all the fields other than "Order" and ı was able to see the posted data.
Am i doing somthing wrong with order configuration.
My model class is
public class VanInformationViewModel : BaseViewModel
{
[SearchableString]
[Sortable(Default = true)]
[Display(Name ="Number Plate")]
public string NumberPlate { get; set; }
[SearchableString]
[Sortable]
[Display(Name = "Driver")]
public string DriverName { get; set; }
[SearchableString]
[Sortable]
[Display(Name = "Helper")]
public string HelperName { get; set; }

    [Display(Name = "Driver Contact")]
    public string DriverContactNumber { get; set; }
    [Display(Name = "Helper Contact")]
    public string HelperContactNumber { get; set; }
}

Id and CreatedDate properties are in my base class and these are the fields i dont want to show on grid.
Regards
Hunain

i am searching date in format mm/dd/yyyy and in db the dates are in format like yyyy-mm-dd hh-mm-ss...but it's not working as excepted...Need Help stuck my work

Code ;-
table.columns().every(function (index) {
$('#test tr:last th:eq(' + index + ') input')
.on('keyup change',
function (e) {
table.column($(this).parent().index() + ':visible').search(this.value).draw();
});
});

Hi,

i'm trying to set the default sortable column with the tag
[Sortable(Default = true)] but it don't seam to do anything. I downloaded the demo project and it don't seam to work.

Is [Sortable(Default = true)] attribute design to replace the order element ?
$('#example').DataTable( {
"order": [[ 3, "desc" ]]
} );

Thx

Search using "co" (contains) is often inefficient. Adding support for Like comparisons would be a great enhancement. Some "user instruction" for those unfamiliar with SQL Like operations is advised.

I've successfully tested one possible implementation that requires adding a dependancy for Microsoft.EntityFrameworkCore 3.19. The approach I used was to piggy-back on the "contains" path and use Like when a wildcard is detected in the search term. It is of note that I've only tested this with string values. I remember reading somewhere that casting to string is not automatic when performing like operations with numeric value.

The following code is added to your StringSearchExpressionProvider class.

// Like Method Info declaration
private static readonly MethodInfo _likeMethod = typeof(DbFunctionsExtensions)
.GetMethod("Like", new[] { typeof(DbFunctions), typeof(string), typeof(string) });

// Supported wildcard characters
private static char[] likeOperators = { '%', '_', '[' };

The switch statement in GetComparison method is updated as follows. Note that I also use Like for the "StartsWith" operations.

        switch (op.ToLower())
        {
            // JAMX case StartsWithOperator: return Expression.Call(left, StartsWithMethod, right, IgnoreCase);
            case StartsWithOperator:                    
                if (term.IndexOfAny(likeOperators) != -1)
                {
                    return Expression.Call(null, _likeMethod, Expression.Constant(EF.Functions), left, right);
                }                    
                return Expression.Call(left, StartsWithMethod, right);
            case ContainsOperator:                    
                if (term.IndexOfAny(likeOperators) != -1)
                {
                    return Expression.Call(null, _likeMethod, Expression.Constant(EF.Functions), left, right);
                }
                return Expression.Call(left.TrimToLower(), _stringContainsMethod, right.TrimToLower());
            case EqualsOperator: return Expression.Equal(left.TrimToLower(), right.TrimToLower());
            default: return base.GetComparison(left, op, right);
        }

A limitation of this approach is that it only supports the single parameter Like operation provided by DbFunctionsExtensions. The drawback is apparent if you are searching for a string containing a wildcard character. The two parameter operation allows you to specify an escape character (e.g. Linq example: query = query.Where(o => EF.Functions.Like(o.OrderNumber, OrderNumber, "\\")); ). Easily compensated for by adjusting the user documentation to define an acceptable escape character for your implementation and adjusting the MethodInfo declaration.

One more thing. I'm not sure if it is my environment, but the "sw" - StartsWith feature does not work when using the two parameter variant with the string comparison method parameter. I redefined the MethodInfo declaration as follows to fix it:

private static readonly MethodInfo StartsWithMethod = typeof(string)
.GetMethods()
.First(x => x.Name == "StartsWith" && x.GetParameters().Length == 1);
// .First(x => x.Name == "StartsWith" && x.GetParameters().Length == 2);

Is your feature request related to a problem? Please describe.
I think its better to target .NET Standard 2.0 or 2.1 instead of a specific framework as using a specific framework would reduce the availability of the Library. As any project that does not target .NET Core 3.0 or above would not be able to use it.

As shown in the above image. If .NET Standard 2.0 or 2.1 is targeted it would reach a wider audience. Generally speaking it is always better for a library to be written using .NET Standard instead of targeting a specific framework when possible.

Describe the solution you'd like
I hope we could revert back to .NET Standard 2.0 instead of targeting .NET Core 3.0

...this could be done as a standard solution, with program.cs, startup.cs etc. so can be run directly from Visual Studio?

Many thanks.

Hi,

it more a question than a bug.

The software that i'm building have multiple language. I use the attribute tag [DisplayName("Type")]. When I render the table manually with @Html.DisplayFor the text is localized. But when I use your tag helper it not.

In your tag helper line 60 headerRow.AppendLine($"{column.Name}");is it possible to use column.DisplayName ? Or maybe we can implement the Localized directly in that class too.

What do you think ?

Best,

There is a plugin "RowGroup",

Is there any possibility to pass grouping settings from server and apply over it?

Hi Abdul,

How can i search custom property columns?

For example;

I have an enum in the entity model like this.

public enum ItemType : short {
	[Display(Name = "Item Card")]
	InventoryCard = 0,
	[Display(Name = "Service Card")]
	ServiceCard = 4
}

[DisplayName("Item Type")]
[Sortable]
[SearchableString]
public string ItemType { get; set; }

CreateMap<ItemTransfer, ItemCardDataTableModel>()
     .ForMember(dest => dest.ItemType, opt => opt.MapFrom(src => src.ItemType.GetDisplayName()))......

I want to search display name on the datatable but i am getting error like this;

Because UI property type is string and the entity property type is short.

Other Example;

I have a name and surname property in the entity model.
I want to show Name + Surname => NameSurname column on the UI. How can i search this column?

Thanks.

Activating DataTable DOM buttons and set in DataTable Api paging to true, fails.
Only shows 1 page.

Hi Rahman, I want to find out if the grid can support advanced searches outside the grid

Hi Abdul,

thank you for the nice package! I was looking around for quite some time until I stumbled across your quite new project. It seems there are not as many useful and up to date projects concerning DataTables in ASP.NET (Core, MVC, ...) as you'd expect and like.

The part that annoys me the most about the current DataTable packages is that you always have to write the client-side code individually and separately from the server-side code for each table although it would make sense to generate the code based on what is delivered by the server. Meaning it should be possible to generate at least the

columns: [
    {
        title: "Name",
        data: "Name",
        name: "eq",
        sortable: true,
        searchable: true
    },
    {
        title: "Position",
        data: "Position",
        name: "co",
        sortable: true,
        searchable: true
    }
]

part of the client-side code based on what is declared on the server-side, saving a lot of time and reducing the risk of introducing errors.

What I've learned from other languages such as PHP, this approach works great and isn't even too hard to implement. In one of my projects, I'm using a package called yajra/laravel-datatables (plus some extension packages) which provides a way to wrap everything related to a datatable into one class which is then used to display the table, provide table data, filter the data and transform the output if necessary. The code for a basic table looks like this:

<?php

namespace App\DataTables;

use App\Models\User;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\URL;
use Yajra\DataTables\Services\DataTable;

/**
 * Class UserDataTable
 *
 * @package App\DataTables
 */
class UserDataTable extends DataTable
{
    /**
     * Build DataTable class.
     *
     * @param mixed $query Results from query() method.
     * @return \Yajra\DataTables\DataTableAbstract
     */
    public function dataTable($query)
    {
        return datatables($query)
            ->addColumn('roles', function (User $user) {
                return $user->roles->implode('name', ', ');
            })
            ->editColumn('created_at', function (User $user) {
                return format_date($user->created_at);
            })
            ->addColumn('action', 'users.components.action_column');
    }
    /**
     * Get query source of dataTable.
     *
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function query()
    {
        return User::with('usergroups');
    }
    /**
     * Optional method if you want to use html builder.
     *
     * @return \Yajra\DataTables\Html\Builder
     */
    public function html()
    {
        return $this->builder()
            ->columns($this->getColumns())
            ->addAction()
            ->ajax(['type' => 'POST'])
            ->parameters(array_merge(
                $this->getBuilderParameters(),
                [
                    'fixedColumns' => [
                        'leftColumns' => 1,
                        'rightColumns' => 1,
                    ],
                ]
            ));
    }
    /**
     * Get columns.
     *
     * @return array
     */
    protected function getColumns()
    {
        return [
            ['data' => 'id', 'title' => trans('users.table.header.id')],
            ['data' => 'name', 'title' => trans('users.table.header.name')],
            ['data' => 'email', 'title' => trans('users.table.header.email')],
            ['data' => 'roles', 'searchable' => false, 'orderable' => false, 'title' => trans('users.table.header.roles')],
            ['data' => 'created_at', 'title' => trans('users.table.header.created_at')],
        ];
    }
    /**
     * Get filename for export.
     *
     * @return string
     */
    protected function filename()
    {
        return date('Ymd_His') . '_users';
    }
}

A little explanation:

• The method dataTable($query) takes the results of a query and transforms them. Translated to C#, it means we'd pass something like a list or collection which can be manipulated. Maybe with some nice convenience methods.
• The method query() returns the base query which can be manipulated with filtering, sorting, etc. Translated to C#, this would be a LINQ query.
• The method html() is used to build the client-side code for the datatable. The builder can have some global defaults and a variety of overrides as you can see.
• The method getColumns() returns a list of columns. This one is tricky to explain, because I think we actually don't need it. We could build the same data based on attributes of a view model for example.
• The method filename() is only there to provide a way of customizing the file name of exports like XLSX, CSV, ...

In the controller we only have to render the table with return $dataTable->render('users.list'); which uses a view template called users.list where we only have to print the table and the script in the desired location:

<div class="row">
    <div class="col-md-12">
        <div class="box box-default">
            <div class="box-body">
                {{ $dataTable->table([], true) }}
            </div>
        </div>
    </div>
</div>

@push('scripts')
    {!! $dataTable->scripts() !!}
@endpush

I'd like to hear what you think about implementing something similar in ASP.NET. In my opinion, this would be somewhat of a game changer if done properly as it makes building DataTables really a breeze.

Hi, are there plans to release an update for core 3.0 ?

Actually show this error on launch:

"Could not load type 'Microsoft.AspNetCore.Mvc.MvcJsonOptions' from assembly 'Microsoft.AspNetCore.Mvc.Formatters.Json, Version=3.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60"

thanks

There was a slight learning curve, but once I understood it; it's a fantastic tool that I'm going to keep coming back to. I'd like to send you some BAT (it's the only crypto I have PM me for details).

I have a request to export as a general .csv instead of excel.

Hi,
Thanks for your great job! really usefull
when working with Nullable properties makes it impossible to filter on the nullable property.

ps: I've tried to clone it and fix the issue but could not use it as I do not find all objects (i.e. DTParameters)

A suggested enhancement to sorting.
Have you thought about a way to enable natural sorting? Could this potentially be done through the "SortOptionsProcessor"?

Example 1
-- this will return result that we DO NOT want
select a from (values ('10.abc'), ('2.abc')) T(a) order by a asc;

-- but the following will return the result EXPECTED
select a from (values ('0010.abc'), ('0002.abc')) T(a) order by a asc;

Example 2
SELECT * FROM #varchar_field ORDER BY CASE WHEN ISNUMERIC(mixed_field) = 1 THEN CAST(mixed_field AS FLOAT) WHEN ISNUMERIC(LEFT(mixed_field,1)) = 0 THEN ASCII(LEFT(LOWER(mixed_field),1)) ELSE 2147483647 END

Otherwise, thanks for a great package.

First launch Skip fails. Is negative number.
table.Start value is Zero for first page / resultset. (Zero -1) is negative number.
var items = await query
.AsNoTracking()
.Skip(table.Start - 1 * table.Length)
.Take(table.Length)
.ProjectTo(_mappingConfiguration)
.ToArrayAsync();
return items;

Describe the question
A clear and concise description of what the question is. In individual column searching i'm facing the issue in removing column header in search placeholder.

Describe the question
A clear and concise description of what the question is.
I'm getting result using the query:-IQueryable query = this.ApplicationDbContext.LocalSAO.FromSqlRaw($"{Constants.SPName.GetData} @name,@title", sqlParam.ToArray());
its working well. but in next step:- i.e var size = query.Count();
i'm getting exception like:-"FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it. Consider calling AsEnumerable after the FromSqlRaw or FromSqlInterpolated method to perform the composition on the client side."

To Produce
Steps to produce the behavior:

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. How this can be done?

Expected behavior
A clear and concise description of what you expect to happen along with your view model and domain model. Please note, questions without view model and domain model will be closed without any further discussion. Please check the example project before posting. Most of the times, you would find the answer in example solution.

Screenshots
If applicable, add screenshots to help explain your question.

Desktop (please complete the following information):

• OS: [e.g. Windoes10]
• Browser [e.g. chrome]

Smartphone (please complete the following information):

• Device: [e.g. iPhone6]
• OS: [e.g. iOS8.1]
• Browser [e.g. stock browser, safari]
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

Hey @fingers10,

today I was trying to implement the JqueryDataTablesPagedResults in my API and I ran into an issue.

I have an API that return an object that contains more data that the viewModel that I use to display the table itself. It working good to display the data, but the search and sort won't work.

Example :

The call on the API side look like the example
public async Task<JqueryDataTablesPagedResults> GetPage(JqueryDataTablesParameters tableParams, IQueryable query)
{
Response[] items = null;

        query = SearchOptionsProcessor<Response, TEntity>.Apply(query, tableParams.Columns);
        query = SortOptionsProcessor<Response, TEntity>.Apply(query, tableParams);
        
        var size = await query.CountAsync();

        if (tableParams.Length > 0)
        {
            items = await query
            .Skip((tableParams.Start / tableParams.Length) * tableParams.Length)
            .Take(tableParams.Length)
            .ProjectTo<Response>(_mappingConfiguration)
            .ToArrayAsync();
        }
        else
        {
            items = await query
            .ProjectTo<Response>(_mappingConfiguration)
            .ToArrayAsync();
        }

        return new JqueryDataTablesPagedResults<Response>
        {
            Items = items,
            TotalSize = size
        };
    }

TEntity is for example a class Employee with FirstName, LastName, Phone and Sexe, CreateDate, Modify By.
Response in a class EmployeeResponse with only FirstName, LastName, Phone and Sexe.

On the client side, the method
var results = await torosApiFacade.GetPageByPost(url, param);
return new JsonResult(new JqueryDataTablesResult
{
Draw = param.Draw,
Data = results.Items,
RecordsFiltered = results.TotalSize,
RecordsTotal = results.TotalSize
});

Where ViewModel is the EmployeeResponse class minus the Sexe with all the JqueryDataTable DataAnnotation.

I receive the data #1 and it working. But when I enter a value in any search column, it not working in the API side, I still receive all the data. I look like the SearchOptionsProcessor don't work since the EmployeeResponse is not exactly like the EemployeeViewModel.

If I create the EmployeeViewModel directly in my api and use it with SearchOptionsProcessor it working #1. Since the front end and the api model is the same. But in real case my API don't have and don't need to know my ViewModel.

Expected behavior
Since my API can't have a model for each my Front End view I would expect it to work since my Entity have all the ViewModel data and more. Let me know if I do something wrong here.

Desktop (please complete the following information):

• OS: Window
• Browser Chrome

Additional context
.Net Core 3.1 with latest Nuget JqueryDataTable version 4

Thx

It seems that the columns are rendered as there are found in the recursive search of the model.

It would be great to have an annotation like [ColumnOrder (int)] to define it.

Is there any way an existing stored procedure could be supplied as the record set (assuming it support the required parameters for paging etc.)?

My table output brings together data from many table joins and so would benefit from server paging but can't see how I would apply your code to my existing sproc.

Hi,

I have this Model

public class Patient
    {
        public virtual string UserId { get; set; }
        public virtual ApplicationUser User { get; set; }
    }
public class ApplicationUser : IdentityUser
    {
        public string PhoneNumber { get; set; }
    }

And My ViewModel looks like this

public class PatientVM
    {
        [Sortable]
        [SearchableString]
        //this property is mapped from User.PhoneNumber
public string PhoneNumber { get; set; }

        public void Mapping(Profile profile)
        {
            profile.CreateMap<Subscriber, SubscribersListDto>()
                .ForMember(destination => destination.PhoneNumber, member => member.MapFrom(source => source.User.PhoneNumber));;
        }

when searching for a PhoneNumber which is mapped from User.PhoneNumber I got this error
because the Vm has a flatter hierarchy and my Base Model is nested as shown in the example above

An unhandled exception has occurred while executing the request.
System.Reflection.AmbiguousMatchException: Multiple custom attributes of the same type found.
   at System.Attribute.GetCustomAttribute(MemberInfo element, Type attributeType, Boolean inherit)
   at System.Reflection.CustomAttributeExtensions.GetCustomAttribute[T](MemberInfo element)
   at JqueryDataTables.ServerSide.AspNetCoreWeb.Infrastructure.SearchOptionsProcessor`2.GetTermsFromModel(Type parentSortClass, String parentsEntityName, String parentsName, Boolean hasNavigation)+MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at JqueryDataTables.ServerSide.AspNetCoreWeb.Infrastructure.SearchOptionsProcessor`2.GetValidTerms(IEnumerable`1 columns)+MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at JqueryDataTables.ServerSide.AspNetCoreWeb.Infrastructure.SearchOptionsProcessor`2.Apply(IQueryable`1 query, IEnumerable`1 columns)

Any way to make searching work for this kind of implementation
Thanks.

How can I add multi-select checkboxes to table? The link below shows how to do it for a regular html/js datatable.
You basically create a fake/empty column and say that it will host selection checkboxes. This .NET library creates all columns dynamically and I see no way to add that fake column without adding fake property to my data model.

https://datatables.net/extensions/select/examples/initialisation/checkbox.html

Describe the bug
I tried using your methods for searching and sorting server functionality. Sorting works all right, but I get exception for searching method which says "Invalid Operator 'Explanation'", where Explanation is class property. I compared to your demo project but I cannot find difference nor what causes problem.
Here below are code snippets.

Stack trace

System.ArgumentException: Invalid Operator 'Explanation'.
   at JqueryDataTables.ServerSide.AspNetCoreWeb.Providers.DefaultSearchExpressionProvider.GetComparison(MemberExpression left, String op, Expression right)
   at JqueryDataTables.ServerSide.AspNetCoreWeb.Infrastructure.SearchOptionsProcessor`2.GetComparisonExpression(SearchTerm term, ParameterExpression obj)
   at JqueryDataTables.ServerSide.AspNetCoreWeb.Infrastructure.SearchOptionsProcessor`2.Apply(IQueryable`1 query, IEnumerable`1 columns)
   at Tamber.Repositories.PostRepository.GetReportsPaged(JqueryDataTablesParameters parameters) in /Users/vpetrovic/Documents/Projects/Web/Tamber/Tamber/Repositories/PostRepository.cs:line 443
   at Tamber.Services.PostService.GetPagedReportsForAdminPanel(JqueryDataTablesParameters param) in /Users/vpetrovic/Documents/Projects/Web/Tamber/Tamber/Services/PostService.cs:line 592
   at Tamber.Areas.AdminPanel.Controllers.PostController.GetPagedReports(JqueryDataTablesParameters param) in /Users/vpetrovic/Documents/Projects/Web/Tamber/Tamber/Areas/AdminPanel/Controllers/PostController.cs:line 47
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

This is a repository method where I call two methods for sorting and searching

        public async Task<List<PostReport>> GetReportsPaged(JqueryDataTablesParameters parameters)
        {
            IQueryable<PostReport> query = _context.PostReports
                .Include(pr => pr.ReportedBy)
                .Include(pr => pr.Post)
                .ThenInclude(p => p.PostedBy);
            
            query = SearchOptionsProcessor<PostReportDTO, PostReport>.Apply(query, parameters.Columns);
            query = SortOptionsProcessor<PostReportDTO, PostReport>.Apply(query, parameters);
                
            return await query
                .Skip((parameters.Start / parameters.Length) * parameters.Length)
                .Take(parameters.Length)
                .ToListAsync();
        }

This is my PostReport Model

    public class PostReport
    {
        [Key]
        public long Id { get; set; }
        
        public DateTime DateTime { get; set; }
        
        public long PostId { get; set; }
        
        [ForeignKey(name: "PostId")]
        public Post Post { get; set; }
        
        public string ReportedById { get; set; }
        
        [ForeignKey(name: "ReportedById")]
        public ApplicationUser ReportedBy { get; set; }

        public string Explanation { get; set; }
        
        public bool Resolved { get; set; }
    }

This is PostReportDTO class

    public class PostReportDTO
    {
        public long Id { get; set; }
        
        [Searchable]
        [Sortable]
        public DateTime DateTime { get; set; }
        
        public ElementDto ReportedBy { get; set; }

        public PostDTO Post { get; set; }
        
        [Searchable]
        [Sortable]
        public string Explanation { get; set; }
        
        public bool Resolved { get; set; }
    }

Desktop (please complete the following information):

• OS: [e.g. iOS] MacOS Big Sur
• Browser [e.g. chrome, safari] Chrome
• Version [e.g. 22] 91
• .NET Core 3.1

First of all, thank you very much for your code. It allowed me to prototype something in hours as opposed to days (possibly weeks),

When more than one search columns contains multiple terms the search results are not being applied correctly. Take as an example my particular implementation where I allow searches by an individual's first and last name. My data set also included MaidenName, NickName, and MiddleName so the application automatically searches these other column as follows:

• When the user searches on LastName the application also searches MaidenName.
• When the user searched on FirstName, the application also searches on MiddleName and NickName.

Searching of these "other" columns is disabled,

My view model contains the following definitions for the LastName and FirstName columns:

[IncludeInReport(Order = 1)]
[JqueryDataTableColumn(Order = 3)]
[SearchableString(EntityProperty = "LastName,MaidenName")]
[Sortable(EntityProperty = "LastName", Default = true)]
[DisplayName("Last Name")]
public string LastName { get; set; }

[IncludeInReport(Order = 2)]
[JqueryDataTableColumn(Order = 4)]
[SearchableString(EntityProperty = "FirstName,MiddleName,NickName")]
[Sortable(EntityProperty = "FirstName", Default = true)]
[DisplayName("First Name")]
public string FirstName { get; set; }

Searching on either FirstName or LastName works exactly as expected. Searching on one of FirstName or LastName plus any number of other column that do not have multiple terms also works as expected. Searching on both LastName and FirstName, however, does not return the expected results.

Searching for LastName = "Smith" and FirstName = "Tony" results in the following search expression (copied directly from the query object in the debugger after SearchOptionsProcessor.Apply).

{IndividualSearch => ((True AndAlso ((False OrElse IndividualSearch.LastName.Trim().ToLower().Contains("smith".Trim().ToLower())) OrElse IndividualSearch.MaidenName.Trim().ToLower().Contains("smith".Trim().ToLower())))
AndAlso (((((False OrElse IndividualSearch.LastName.Trim().ToLower().Contains("smith".Trim().ToLower())) OrElse IndividualSearch.MaidenName.Trim().ToLower().Contains("smith".Trim().ToLower())) OrElse IndividualSearch.FirstName.Trim().ToLower().Contains("tony".Trim().ToLower())) OrElse IndividualSearch.MiddleName.Trim().ToLower().Contains("tony".Trim().ToLower())) OrElse IndividualSearch.NickName.Trim().ToLower().Contains("tony".Trim().ToLower())))}

As you can see in the search expression following "AndAlso" the filter expression for the FirstName columns (FirstName,MiddleName,NickName) also includes the LastName columns (LastName,MaidenName shown in bold above) which results in the FirstName search term being ignored.

I'm using:
Visual Studio 2019
dotnetcore 3.1
EntityFrameWorkCore.SQLServer 3.1.5
MS Edge

Can you add in class "JqueryDataTablesHtmlLocalizedTagHelper" search by html tag "select" using class "Microsoft.AspNetCore.Mvc.Rendering.SelectList" in ViewModel?
Example in asp net core:
Example in Datatables.net: https://www.datatables.net/examples/api/multi_filter_select.html

Thank you

Describe the question
A clear and concise description of what the question is.
is it possible to get the buttons using jquery datatables near paging like-

Cancel and Ok Buttons...Help would be appreciated.

Describe the bug
Problem with expression that is created when trying to search with properties that are type of enum. Ef core 3.0 does allow only for evaluating queries on one side - server side (app side) or database side. Query that i am receiving from example app you have provided after adding sql provider is:
value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[AspNetCoreServerSide.Models.DemoEntity]).AsNoTracking().Include(x => x.DemoNestedLevelOne).ThenInclude(y => y.DemoNestedLevelTwo).Where(DemoEntitySearch => (True AndAlso DemoEntitySearch.Position.ToString().Equals(Accountant.ToString(), OrdinalIgnoreCase)))
I think that the problem is with ToString().Equals(Accountant.ToString() - it cannot be evaluated on database side which means that all the stuff has to be loaded to memory before we can filter which can lead to performance issues.

To Reproduce
Steps to reproduce the behavior:
Instead of using in memory provider as database use sql server or any other provider that is connecting to real database. Then try to filter table on enum type.

Expected behavior
Properly translated query for enum types so there won't be any drawback using this plugin.

Screenshots
N/A

Desktop (please complete the following information):
N/A

Smartphone (please complete the following information):
N/A

Additional context
N/A

Hello. I'm sorry, but not understand, how add to Demo project localization by resources-files.. I make localization using interfaces IStringLocalizer and IStringLocalizerFactory for keep resources in database. Can you add localization by resources-files.Thanks

Hi,
Actually I need to add Action Column which will have View/Edit/Delete functionality.
I am not able to add same.
I am able to see Action column as I have added Action Property in Model but row wise Hyperlinks are not coming up.
Below is code i am trying.

can you please give some suggestion how can i add hyperlink action column?

var table = $('#runningJob').DataTable({
language: {
processing: "Loading Data...",
zeroRecords: "No matching records found"
},
processing: true,
serverSide: true,
orderCellsTop: true,
autoWidth: true,
deferRender: true,
lengthMenu: [5, 10, 15, 20],
dom: '<"row"<"col-sm-12 col-md-6"B><"col-sm-12 col-md-6 text-right"l>><"row"<"col-sm-12"tr>><"row"<"col-sm-12 col-md-5"i><"col-sm-12 col-md-7"p>>',
buttons: [
{
text: 'Export to Excel',
className: 'btn btn-sm btn-dark',
action: function (e, dt, node, config) {
window.location.href = "/RunningJobs/GetExcel";
},
init: function (api, node, config) {
$(node).removeClass('dt-button');
}
}
],
ajax: {
type: "POST",
url: '/RunningJobs/LoadTable',
contentType: "application/json; charset=utf-8",
async: true,
headers: {
"XSRF-TOKEN": document.querySelector('[name="__RequestVerificationToken"]').value
},
data: function (data) {
let additionalValues = [];
additionalValues[0] = "Additional Parameters 1";
additionalValues[1] = "Additional Parameters 2";
data.AdditionalValues = additionalValues;

                return JSON.stringify(data);
            }
        },
        columns: [
            {
                data: "JobNumber",
                name: "eq",

            },
            {
                data: "JobID",
                name: "co"
            },
            {
                data: "Description",
                name: "co"
            },
            {
                data: "DiaryConfig",
                name: "eq"
            },
            {
                data: "Type",
                name: "eq"
            },
            {
                data: "NextRunDate",
                name: "eq"
            }                      
            ,
            {                    
                data: "Action",
                render: function (data, type, full, meta) {
                    return '<a class="btn btn-info" href="/DiaryConfig/EditDiaryEntry/' + full.JobID + '">Edit</a>';
                }
            }
        ]          
    });

Hi,

I cant find out how I am supposed to deal with the below scenario.

I am mapping a datamodel to a viewmodel like this:
CreateMap<DataModel, MyViewModel>() .ForMember(m => m.YearSeason, map => map.MapFrom(u => $"{u.Year}-{u.Season}")) .ReverseMap();

And adding searchoptions like this:
query = SearchOptionsProcessor<MyViewModel, DataModel>.Apply(query, parameters.Columns);

Resulting in this error that I understand but how do I deal with an issue like this?

Instance property 'YearSeason' is not defined for type 'Models.DataModel'
Parameter name: propertyName

Thanks in advance
Axel G