Query predicate to search related record if it exists
9 Replies
Hi Koen,
Thanks for contacting Syncfusion support.
Before we proceed with your requirement we need the following details,
1. Share your complete Grid rendering code.
2. Share the dataSource Structure that you have bounded to the Grid.
3. Is ‘ParentProduct’ is a foreignKey column in the Grid?
4. Do you need perform searching operation for the foreignKey value of the Grid?5. Share the video demonstration of your requirement.
Regards,Shalini M.
Dear,
I have made a demo solution (download here)
I must admit I have made a mistake by saying I was using a datagrid. In fact, I am having this issue with a dropdown.
In the dropdown, I show a customer, and if that customer has a parent customer, it is also shown:
When searching for "Gio" I want it to show up two results: "Giovanni Rovelli" and "Anabela Domingues (child of Giovanni Rovelli)".
However it returns "Anabela Domingues (child of Giovanni Rovelli)" AND all customers without a parent customer...
This is the filtering function:
function onfilteringcustomercombobox(e) { var CBObj = document.getElementById("customercombobox").ej2_instances[0]; var words = e.text.split(" "); if (words.length >= 1) { var queryPredicate = new ej.data.Predicate('ContactName', 'equal', 'xxx'); for (i = 0; i < words.length; i++) { var word = words[i]; if (word !== '') { queryPredicate = queryPredicate.or('ContactName', 'contains', word); queryPredicate = queryPredicate.or('ParentCustomer.ContactName', 'contains', word); } } var query = new ej.data.Query().where(queryPredicate); e.updateData(CBObj.dataSource, query); } };
This is the resulting query:
SELECT [t].[CustomerID], [t].[Address], [t].[City], [t].[CompanyName], [t].[ContactName], [t].[ContactTitle], [t].[ParentCustomerID], [Customer.ParentCustomer].[CustomerID], [Customer.ParentCustomer].[Address], [Customer.ParentCustomer].[City], [Customer.ParentCustomer].[CompanyName], [Customer.ParentCustomer].[ContactName], [Customer.ParentCustomer].[ContactTitle], [Customer.ParentCustomer].[ParentCustomerID]
FROM (
SELECT TOP(@__p_0) [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[ParentCustomerID]
FROM [30000Records] AS [c]
) AS [t]
LEFT JOIN [30000Records] AS [Customer.ParentCustomer] ON [t].[ParentCustomerID] = [Customer.ParentCustomer].[CustomerID]
WHERE ((CASE
WHEN [t].[ContactName] = N'xxx'
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END | CASE
WHEN CHARINDEX(N'gio', COALESCE([t].[ContactName], N'Blanks')) > 0
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END) | CASE
WHEN [t].[ParentCustomerID] IS NULL OR ([t].[ParentCustomerID] IS NOT NULL AND (CHARINDEX(N'gio', COALESCE([Customer.ParentCustomer].[ContactName], N'Blanks')) > 0))
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END) = 1
The highlighted part causes it to return all customers without a parentcustomer...
A small question as a side note:
When I uncomment the "Take(30)" part in the following action, I get a "nullreference error":
public IActionResult UrlDatasource([FromBody] DataManagerRequest dm) { EFDataContext db = new EFDataContext(); IQueryable<Customer> DataSource = db .Customers .Include(c => c.ParentCustomer) //.Take(30) ; QueryableOperation operation = new QueryableOperation(); if (dm.Search != null && dm.Search.Count > 0) { DataSource = operation.PerformSearching(DataSource, dm.Search); //Search } if (dm.Where != null && dm.Where.Count > 0) //Filtering { DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator); } int count = DataSource.Count(); if (dm.Skip != 0) { DataSource = operation.PerformSkip(DataSource, dm.Skip); //Paging } if (dm.Take != 0) { DataSource = operation.PerformTake(DataSource, dm.Take); } return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource); }
the error
why is that?
SIGN IN To post a reply.
