This is a migrated thread and some comments may be shown as answers.

RadGrid.GetBindingData and Entity Framework

29 Answers 566 Views

Grid

This is a migrated thread and some comments may be shown as answers.

Gabriel asked on 10 Oct 2012, 12:52 PM

20121010-947.png

20121010-951.png

Hello everyone,

I've been researching on how to bind an Object Data Source to a RadGrid, implementing sorting, filtering and paging.

So I did the following

OdsProducts (Object DataSource):

using System.Collections.Generic;

using System.Linq;

using SetupSvc;

/// <summary>

/// Summary description for DataSources

/// </summary>

public class OdsProducts

{

int _count;

public List<Product> SelectProducts(string filterExpression, string sortExpression, int clientId, int maximumRows, int startRowIndex)

{

//Call to a WCF service

using (SetupSvcClient client = new SetupSvcClient())

{

List<Product> products = client.GetProductGrid(clientId, startRowIndex, maximumRows, sortExpression, filterExpression).ToList();

// client.GetProductListByClientId(1).ToList();

return products;

}

public int CountProducts(string filterExpression, string sortExpression, int clientId)

{

//Don't worry about this, I know it's not right, but I know how to solve it.

using (SetupSvcClient client = new SetupSvcClient())

{

List<Product> allProducts = client.GetProductListByClientId(1).ToList();

return allProducts.Count;

}

ASPX File:

<telerik:RadScriptManager ID="sManager" runat="server"></telerik:RadScriptManager>

<telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">

<AjaxSettings>

<telerik:AjaxSetting AjaxControlID="RadGrid1">

<UpdatedControls>

<telerik:AjaxUpdatedControl ControlID="RadGrid1" LoadingPanelID="RadAjaxLoadingPanel1" />

</UpdatedControls>

</telerik:AjaxSetting>

</AjaxSettings>

</telerik:RadAjaxManager>

<telerik:RadAjaxLoadingPanel runat="server" ID="RadAjaxLoadingPanel1" Skin="Default" />

<div class="box">

<div class="box-head">

<h3>Prueba grid</h3>

</div>

<div class="box-content">

<telerik:RadGrid ID="RadGrid1" DataSourceID="ObjectDataSource1" AllowFilteringByColumn="true"

AllowSorting="true" AllowPaging="true" runat="server" EnableLinqExpressions="true">

<MasterTableView AllowCustomSorting="true" OverrideDataSourceControlSorting="true"></MasterTableView>

</telerik:RadGrid>

</div>

<asp:ObjectDataSource ID="ObjectDataSource1" TypeName="OdsProducts" EnablePaging="true"

SelectMethod="SelectProducts" SelectCountMethod="CountProducts" runat="server"

OnSelecting="ObjectDataSource1_Selecting">

<SelectParameters>

<asp:Parameter Name="filterExpression" Type="String" />

<asp:Parameter Name="sortExpression" Type="String" />

<asp:Parameter Name="clientId" Type="Int32" DefaultValue="2" />

</SelectParameters>

</asp:ObjectDataSource>

Code Behind File:

protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)

{

//e.Arguments.MaximumRows = your value;

e.InputParameters["filterExpression"] = RadGrid1.MasterTableView.FilterExpression;

e.InputParameters["sortExpression"] = RadGrid1.MasterTableView.SortExpressions.GetSortString();

e.InputParameters["clientId"] = this.GetCurrentClientId();

}

Use of RadGrid.GetBindingData

public List<Product> GetProductsGrid()

{

return Telerik.Web.UI.RadGrid.GetBindingData(this

.GetProductIQueryableList().Where(x => x.ClientId == clientId).OrderBy(y=>y.Id), startRowIndex, maximumRows, sortExpression, filterExpression).Data.ToList<Product>();

}

Exception 1
(See attached file 20121010 947.png)

How I solved the .ToString() problem

filterExpression = filterExpression.Replace(".ToString()", string.Empty);

return Telerik.Web.UI.RadGrid.GetBindingData(this.GetProductIQueryableList().Where(x => x.ClientId == clientId).OrderBy(y=>y.Id),

startRowIndex, maximumRows, sortExpression, filterExpression)

.Data.ToList<Product>();

Exception 2
See attached file 20121010 951.png

How can I solve the DateTime.Parse?
Is it possible for you to show us how to use the filterExpression without the RadGrid.GetBindingData?

In advance, many many thanks :D

29 Answers, 1 is accepted

answered on 12 Oct 2012, 01:59 PM

So, I did find this Linq.Dynamic library and it does work with entity framework, you just need to apply the filterExpression in a way this understands.

I created the following method which makes it work. Anyway, if you can find a better solution, please post it here.

Method:

/// <summary>

/// Convierte un FilterExpression enviado por un RadGrid de Telerik a uno válido para ser utilizado

/// por Linq.Dynamic + Entity Framework

/// </summary>

/// <param name="rawFilterExpression">Filter Expression generado por el RadGrid</param>

/// <param name="parameters">Parámetros encontrados dentro del filterExpression y que deben ser utilizados por Linq.Dynamic</param>

/// <returns>Retorna un string válido para ser utilizado por Linq.Dynamic</returns>

public static string ConvertFilterExpression(string rawFilterExpression, out object[] parameters)

{

string filterExpression = rawFilterExpression.Replace(".ToString()", string.Empty);

int count = rawFilterExpression.CountSubStrings("DateTime.Parse");

parameters = new object[count];

if (count > 0)

{

filterExpression = filterExpression.Replace("DateTime.Parse", string.Empty);

for (int i = 0; i < count; i++)

{

System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex("\\(\"\\d{2}/\\d{2}/\\d{4} \\d{1}:\\d{2}:\\d{2}\"\\)");

string date = regex.Match(filterExpression).ToString();

filterExpression = regex.Replace(filterExpression, "@" + i.ToString(), 1);

string[] datePart = date.Replace("\"", string.Empty).Replace(" 0:00:00)", string.Empty).Replace("(", string.Empty).Split('/');

parameters[i] = new DateTime(int.Parse(datePart[2]), int.Parse(datePart[1]), int.Parse(datePart[0]));

}

return filterExpression;

}

This method uses CountSubString which is defined as follows:

public static int CountSubStrings(this string testString, string testSubstring)

{

int count = 0;

if (testString.Contains(testSubstring))

{

for (int i = 0; i < testString.Length; i++)

{

if (testString.Substring(i).Length >= testSubstring.Length)

{

bool equals = testString.Substring(i, testSubstring.Length).Equals(testSubstring);

if (equals)

{

count++;

}

return count;

}

I will still want to know how to make it work with telerik advanced filters, so any help is welcome.

PS:

The method is used this way:

object[] parameters;

string fe = ConvertFilterExpression(filterExpression, out parameters);

iQueryable = iQueryable.Where(fe, parameters);

answered on 17 Oct 2012, 06:32 AM

Hi Gabriel,

I did read your posts and here is what I had in mind: as far as I understand you need to pass RadGrid's filter expression string to an IQueryable. When you have RadGrid bound to Entity Framework objects, RadGrid's filter expression string is constructed in a way different than the when bound to linq entities. That's why you need to convert to the linq type of format if you are to pass it to an IQueryable. The help topic gives the difference between the two.

However, if I am mistaken in my understanding, I'd ask you to open up a formal support ticket and send your project as file attachment - do make it a runnable sample in order to speed up its debugging. Thanks in advance for that.

Greetings, Tsvetoslav
the Telerik team

If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.

answered on 17 Oct 2012, 04:03 PM

Thank you for the reply Tsvetoslav,

I apologize for being rude with you in the last reply, some of the things you are telling me are true:

- I do have to apply the filter expression to an IQueryable object which is formed by Entity Framework using POCO.
- I have to use Object data source because I have to call a WCF service.

Now this is weird:
- According to your documentation, it says a "Contains" filter will generate "@"it.CustomerID LIKE ""%ALFKI%""";" filter expression for an Entity Data Source. I can't use this kind of expression because it is not compatible with Linq.Dynamic library. I need instead something like ".Where("CustomerID.Contains(@0)", myString);" (more like Linq filter expression but with parameters...)

- The function I provided called ConvertFilterExpression works well with the filters I have tested (numeric, string, DateTIme) (BTW, Telerik does not provide DateTIme filter Expressions in the documentation you gave me).

Is ConvertFilterExpression the best way to solve it? do you see a better way?

Thanks

answered on 22 Oct 2012, 01:38 PM

Hi Gabriel,

Yes, you do need conversion to make the predicate passed to the dynamic linq library understandable to it. And in the case of the DateTime objects, it should be in the following format:

(BirthDate < DateTime.Parse("10/6/1948 12:00:00 AM"))

So, if your filter expressions is being set to something like this:

(it.BirthDate > DATETIME'1948-10-06 00:00')

you need to convert it to the above format.

I will give a note to the support team maintaining the help to add the DateTime formats to the topic in question.

All the best, Tsvetoslav
the Telerik team

answered on 27 Oct 2012, 03:09 AM

>> Thank you for the reply but I don't see how this can help me, did you read my posts?

<rant>
Hah, that was exactly the question that ran through my head when I read that reply.
And what's worse, I have somewhat the same question, but related more to using Stored Procedures and filter queries. So I was highly interested in seeing the responses to these very detailed, very clear questions with excellent, supplied example code.
So often in these forums, I see replies that seem to just point us off to some random location. It would be so much more helpful if original replies (i.e., the first reply to a user's question) were more helpful, perhaps by having the replier restate the user's question in his/her own words, state any possible confusions, and then for each confusion, point to A if the problem is X, point to B if the problem is Y, etc. I realize this would be more work on the part of Tech Support, but I think it would make the customer base a lot more happy and productive.
</rant>

answered on 29 Oct 2012, 07:08 PM

Prometheus-2C-Component-Diagram.png

I can't reproduce the code, so I attach an image, a small component diagram of the project.

As you can see, I can't use EntityDataSource directly on the website, I do have to use WCF Services in order to retrieve data.
Currently I'm sending the following data to the WCF server:

int startRowIndex
int maximumRows
string sortExpression
string filterExpression

I will repeat the question. In DAL, how can I use those elements to retrieve the wanted data?

I hope I explained better....

answered on 05 Nov 2012, 11:59 AM

Thanks for the reply,

My DAL is constructed entirely under Entity Framework 4.3.1.
I created methods there, in DAL that accept the following arguments

SelectProducts(string filterExpression, string sortExpression, int maximumRows, int startRowIndex)

Now, picture this:

You have an Entity Context called "Model". How can I use the provided info to filter and sort?
I know you can Skip and Take with "maximumRows" and "startRowIndex" but what about filterExpression and SortExpression.

I am sending both as EnableLinqExpressions="true"
Any Idea?
Should I use Linq.Dynamic or is there another library more suited to solve this problem?

in advance, many thanks.

Accepted

answered on 13 Nov 2012, 12:52 PM

Hello Gabriel,

Following my promise to get back to you with more information, I have received a note from our developers that indeed the GetBindingData function was not designed to work with Linq to Entities and you need to provide a conversion routine for the filter expressions as you have correctly done so already. So, replying to your initial question "Is the ConvertFilterExpression routine" the best approach - yes, that's the best and only approach to this scenario.

Thanks for the understanding.

Regards, Tsvetoslav
the Telerik team

answered on 27 Aug 2013, 05:02 PM

Yes, Rodolfo Sosa Cordero contacted me from europe and we worked on this for a while untill we get a good solution. I implemented it in this way:

OdsData is a generic class I created to return the result and allow paging so, I return the total number of items and the items of the current page in this way

[DataContract(Name="OdsDataFor{0}")]

public class OdsData<T>

{

[DataMember]

public List<T> Data { get; set; }

[DataMember]

public int Count { get; set; }

public OdsData()

{

this.Data = new List<T>();

}

the correct use is if I call an stored procedure:

return base

.Model.GetDCMasterList(clientId).AsQueryable().FilterAndSort(filterExpression, sortExpression, startRowIndex, maximumRows,

true);

if I call a linq query without stored procedure:

OdsData<Store> odsStore = this.GetStoreIQueryable().OrderBy(x => x.Id).FilterAndSort(filterExpression, sortExpression, startRowIndex, maximumRows, false);

(GetStoreIQueryable is a linq call to entity store and returns it as an IQueryable)

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Reflection;

using System.Data.Objects;

using System.Linq.Dynamic;

using Core.Data.AppModel;

using System.Text;

using System.Configuration;

using System.Diagnostics;

using System.Text.RegularExpressions;

public static class UtilsExtensions

{

/// <summary>

/// Convierte un FilterExpression enviado por un RadGrid de Telerik a uno válido para ser utilizado

/// por Linq.Dynamic + Entity Framework

/// </summary>

/// <param name="filterExpression">Filter Expression generado por el RadGrid</param>

/// <param name="parameters">Parámetros encontrados dentro del filterExpression y que deben ser utilizados por Linq.Dynamic</param>

/// <returns>Retorna un string válido para ser utilizado por Linq.Dynamic</returns>

public static string ConvertFilterExpression(string filterExpression, out object[] parameters)

{

List<object> lstParameters = new List<object>();

if (!String.IsNullOrEmpty(filterExpression))

{

//replace ToString()

filterExpression = filterExpression.Replace(".ToString()", String.Empty);

//replace DateTime.Parse

int count = Regex.Matches(filterExpression, "DateTime.Parse").Count;

if (count > 0)

{

//for the DateTime expression to work we need to handle them as

filterExpression = filterExpression.Replace("DateTime.Parse", string.Empty);

for (int i = 0; i < count; i++)

{

//Create a new Regular expression that covers all the date types!

System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(

"\\(\\\"" +

"\\d{1,2}[/-]\\d{1,2}[/-]\\d{2,4}" + //mm/dd/yyyy

"(?:" +

"\\s*\\d{1,2}:\\d{1,2}:\\d{1,2}" + // hh:mm:ss

"(?:" +

"\\s*(?:AM|PM){0,2}" + // AM

"){0,1}" +

"\\\"\\)",

RegexOptions.IgnoreCase | RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled

);

string date = regex.Match(filterExpression).ToString().Replace("\"", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty);

filterExpression = regex.Replace(filterExpression, "@" + i.ToString(), 1);

DateTime d = DTSafeParse(date);

lstParameters.Add(d);

}

parameters = lstParameters.ToArray();

return filterExpression;

}

private static DateTime DTSafeParse(string any)

{

DateTime parsedDate;

DateTime.TryParse(any, out parsedDate);

return parsedDate;

}

public static OdsData<T> FilterAndSort<T>(this IQueryable<T> iqr, string rawFilterExpression, string sortExpression, int startRowIndex, int maximumRows, bool isStoredProcedure)

{

iqr = UtilsExtensions.FilterAndSortIQueryable(iqr, rawFilterExpression, sortExpression);

OdsData<T> ods = new OdsData<T>();

if (isStoredProcedure)

{

ods.Data = iqr.ToList();

ods.Count = ods.Data.Count;

ods.Data = ods.Data.Skip(startRowIndex).ToList();

if (maximumRows > 0)

ods.Data = ods.Data.Take(maximumRows).ToList();

}

else

{

ods.Count = iqr.Count();

iqr = iqr.Skip(startRowIndex);

if (maximumRows > 0)

ods.Data = iqr.Take(maximumRows).ToList(); // se deshace de lo innecesario.

else

ods.Data = iqr.ToList();

}

return ods;

}

internal static IQueryable<T> FilterAndSortIQueryable<T>(this IQueryable<T> iqr, string rawFilterExpression, string sortExpression)

{

if (!string.IsNullOrEmpty(sortExpression))

iqr = iqr.OrderBy(sortExpression);

if (!string.IsNullOrEmpty(rawFilterExpression))

{

object[] parameters;

string fe = Tools.UtilsExtensions.ConvertFilterExpression(rawFilterExpression, out parameters);

iqr = iqr.Where(fe, parameters);

}

return iqr;

}

I hope you understand, I actually don't have so much time to clear things up, so any doubt just ask.

I don't know if it will work with custom properties though :/

answered on 27 Aug 2013, 06:58 PM

Hey there. I never really tried to query combined fields but perhaps it wont be as troubling. Bottom line what you need to do is get the "filterexpression" field from the Radgrid and use Dynamic Linq for the magic. Then once you have that expression run it through the function that Gabriel posted earlier to translate it. Finally, take the translated filterexpression (string) and apply it to the query using the dynamic extension: var result = query.Where(translatedFilterExpression, parameters).

I know this is a bit high level but I just have a few minutes b4 I need to get back to work. Hopefully it'll be enough to get you going.

RadGrid.GetBindingData and Entity Framework

29 Answers, 1 is accepted

The specified type member 'FullName' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.