So, this problem potentially has several causes, but it would be easy to fix (for now) by having CompareTo working properly:

I've implemented ExpandNode for EF7. It works fine, but because of the new implementation of .Include in EF7, it relies on being applied to the EntityQuery with the same type parameter as the entity. For some reason, when a Filter node is executed, it always changes the type parameter to "object". This wouldn't be a problem, except that it requires ExpandNode to execute first.
FilterNode has the following implementation of CompareTo:

 public override int CompareTo(TreeNode other)
        {
            if (other is FilterNode)
            {
                return 0;
            }

            return -1;
        }

Obviously this will result in a stable/consistent ordering only in trivial cases and in my particular case, FilterNode will always end up first regardless of the implementation of ExpandNode's CompareTo, which breaks ExpandNode.

So, first of all, it seems like the solution to the CompareTo is to give each node some sort of priority and sort by that.

Secondly, I've figured out how to preserve the types of the queries. This can be done by calling Queriable.CreateQuery in its generic form by reflection, since the non-generic form returns IQueriable. I'll create a pull request for this.

with or without option server sends just array of items.
I got nuget package and set [LinqToQueryable] above action. Is anything else needed?

Trying to use inline count:

var asQueryable = new List
{
"one",
"two",
"three"
}.AsQueryable();

        var linqToQuerystring = asQueryable.LinqToQuerystring("?$top=1&$skip=1&$inlinecount=allpages");

throws a:

System.InvalidCastException: Unable to cast object of type 'System.Collections.Generic.Dictionary2[System.String,System.Object]' to type 'System.Linq.IQueryable1[System.String]'.

Expected to get results with "3" and "two".

if I remove the inlinecount piece no error is thrown.

I am getting the below error when querying with the following querystring
http://localhost:45442/api/v1/employees?$select=phone,employeeid&$orderby=phone

Whenever including an orderby with another operation I get the below error.

http://localhost:45442/api/v1/employees?$orderby=phone, this works fine and
http://localhost:45442/api/v1/employees?$select=phone,employeeid, this works fine

{"Message":"An error has occurred.","ExceptionMessage":"Unable to sort because the IComparer.Compare() method returns inconsistent results. Either a value does not compare equal to itself, or one value repeatedly compared to another value yields different results. x: '', x's type: 'TreeNode', IComparer: ''.","ExceptionType":"System.ArgumentException","StackTrace":" at System.Collections.Generic.GenericArraySortHelper1.Sort(T[] keys, Int32 index, Int32 length, IComparer1 comparer)\r\n at System.Array.Sort[T](T[] array, Int32 index, Int32 length, IComparer1 comparer)\r\n at System.Collections.Generic.List1.Sort(Int32 index, Int32 count, IComparer1 comparer)\r\n at System.Collections.Generic.List1.Sort()\r\n at LinqToQuerystring.Extensions.LinqToQuerystring(IQueryable query, Type inputType, String queryString, Boolean forceDynamicProperties, Int32 maxPageSize)\r\n at LinqToQuerystring.WebApi.LinqToQueryableAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)\r\n at System.Web.Http.Filters.ActionFilterAttribute.CallOnActionExecuted(HttpActionContext actionContext, HttpResponseMessage response, Exception exception)\r\n at System.Web.Http.Filters.ActionFilterAttribute.<>c__DisplayClass2.<System.Web.Http.Filters.IActionFilter.ExecuteActionFilterAsync>b__0(HttpResponseMessage response)\r\n at System.Threading.Tasks.TaskHelpersExtensions.<>c__DisplayClass412.<Then>b__40(Task1 t)\r\n at System.Threading.Tasks.TaskHelpersExtensions.ThenImpl[TTask,TOuterResult](TTask task, Func`2 continuation, CancellationToken cancellationToken, Boolean runSynchronously)"}

Here is the server code
[LinqToQueryable]
public IQueryable<DTO.EmployeeDTO> Get()
{
List<DTO.EmployeeDTO> list = new List<DTO.EmployeeDTO>();
using (Regency.Common.Repository.Repository<EF_Models.vEmployee> _repo = new Regency.Common.Repository.Repository<EF_Models.vEmployee>(new RegencyWebApi.EF_Models.jde_db_reggieDB()))
{
var employees = _repo.Filter(p => p.emp_status == "Active");
employees.ToList().ForEach(p =>
{
list.Add(new DTO.EmployeeDTO()
{
Department = p.emp_department.Trim(),
EmployeeID = p.emp_id.Trim(),
Phone = p.emp_phone.Trim()
});
});

            return list.AsQueryable<DTO.EmployeeDTO>();
        }

    }

public class EmployeeDTO
{
public string EmployeeID { get; set; }
public string Phone { get; set; }
public string Department { get; set; }
}

$filter=[InvoiceTotal] gt 50.00

InvoiceTotal is decimal type

Return Specified cast is not valid.

I'm not sure if this is a bug or just my sloppy code but the following query works with Microsoft's OData implementation but crashes when I switch over to LinqToQuerystring

/api/purchases?$orderby=PurchasedOn+desc,+Id+desc

The issue is due to the extra + in the orderby right after the comma. If I remove it the query runs successfully.

With the standard Web API OData offering you can do this:

http://blogs.msdn.com/b/webdev/archive/2013/02/06/protect-your-queryable-api-with-validation-feature-in-asp-net-web-api-odata.aspx

Linq to Querystring needs a similar feature. (also requested by @corneliuskopp)

This query
http://localhost:10931/api/customers?%24inlinecount=allpages&%24top=10&%24filter=substringof('the'%2Ctolower(CompanyName))

returns this exception:

{
Message: "An error has occurred.",
ExceptionMessage: "No valid left node for LinqToQuerystring.TreeNodes.EqualsNode",
ExceptionType: "System.InvalidOperationException",
StackTrace: " at LinqToQuerystring.TreeNodes.Base.TwoChildNode.get_LeftNode() at LinqToQuerystring.TreeNodes.EqualsNode.BuildLinqExpression(IQueryable query, Expression expression, Expression item) at LinqToQuerystring.TreeNodes.FilterNode.BuildLinqExpression(IQueryable query, Expression expression, Expression item) at LinqToQuerystring.Extensions.BuildQuery(TreeNode node, IQueryable& queryResult, IQueryable& constrainedQuery) at LinqToQuerystring.Extensions.LinqToQuerystring(IQueryable query, String queryString, Type inputType, Boolean forceDynamicProperties) at LinqToQuerystring.WebApi.LinqToQueryableAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext) at System.Web.Http.Tracing.Tracers.ActionFilterAttributeTracer.<>c__DisplayClass4.b__1() at System.Web.Http.Tracing.ITraceWriterExtensions.TraceBeginEnd(ITraceWriter traceWriter, HttpRequestMessage request, String category, TraceLevel level, String operatorName, String operationName, Action1 beginTrace, Action execute, Action1 endTrace, Action1 errorTrace) at System.Web.Http.Tracing.Tracers.ActionFilterAttributeTracer.OnActionExecuted(HttpActionExecutedContext actionExecutedContext) at System.Web.Http.Filters.ActionFilterAttribute.CallOnActionExecuted(HttpActionContext actionContext, HttpResponseMessage response, Exception exception) at System.Web.Http.Filters.ActionFilterAttribute.<>c__DisplayClass2.<System.Web.Http.Filters.IActionFilter.ExecuteActionFilterAsync>b__0(HttpResponseMessage response) at System.Threading.Tasks.TaskHelpersExtensions.<>c__DisplayClass412.b__40(Task1 t) at System.Threading.Tasks.TaskHelpersExtensions.ThenImpl[TTask,TOuterResult](TTask task, Func2 continuation, CancellationToken cancellationToken, Boolean runSynchronously)"
}

Any idea?

Error: can not update "Microsoft.AspNet.WebApi.Core 5.1.1" to "Microsoft.AspNet.WebApi.Core 4.0.30506.0". So I had to download LinqToQuerystring archieve and copy LinqToQueryableAttribute.cs to my project. Why such strict dependency for one attribute?!

When I run the MongoDB test I get the "System.FormatException" with message the "Input string was not in a correct format." on this line:

var result = collection.LinqToQuerystring("?$filter=[Age] eq 4", true).ToList();

I tried various combinations but nothing works.

I'm trying to query a simple API built using Web API 2 and EF 6. When I make the request /api/affiliates?$expand=AffiliateType I get the following exception.

{
    "message": "An error has occurred.",
    "exceptionMessage": "The Expand query option is not supported by this provder",
    "exceptionType": "System.NotSupportedException",
    "stackTrace": "   at LinqToQuerystring.TreeNodes.ExpandNode.ModifyQuery(IQueryable query)\r\n   at LinqToQuerystring.Extensions.BuildQuery(TreeNode node, IQueryable& queryResult, IQueryable& constrainedQuery)\r\n   at LinqToQuerystring.Extensions.LinqToQuerystring(IQueryable query, Type inputType, String queryString, Boolean forceDynamicProperties, Int32 maxPageSize)\r\n   at LinqToQuerystring.WebApi.LinqToQueryableAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.OnActionExecutedAsync(HttpActionExecutedContext actionExecutedContext, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Controllers.AuthenticationFilterResult.<ExecuteAsync>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__0.MoveNext()"
}

If I remove $expand from the request and modify the method by adding .Include(a => a.AffiliateType), the navigation property is included in the response.

I'm using the following NuGet packages: LinqToQuerystring 0.7.0.6, linqtoquerystring.entityframework 1.1.1, and LinqToQuerystring.WebApi2 1.4.1.12.

Hello, I did some tests and found out that startswith() is not using regex but should since then it can take into account indexes.

For example I have table with 30 million random documents, they have property Name which is indexed.

?$filter=startswith([Name],'abc') eq true takes 36 seconds

.find({Name : /^abc/}) takes 0 seconds

Can we make sure that whenever we use startswith() it'll always use regex?

I just started using your library in a WebApi project I'm working on to easily give me the $inlinecount feature without all of the WebApi headache supported the ODATA format. I have a service that returns a list of stores sorted by distance from a starting point. They initially look like this when returning from the service:

[
  {
    "Distance": {
      "html": "489 mi",
      "meters": 786342.722733381000,
      "miles": 488.610714945366450160152000,
    },
    "Id": 1733,
    "Name": "Store 1",
  },
  {
    "Distance": {
      "html": "513 mi",
      "meters": 824963.241224736000,
      "miles": 512.608392555997748205312000,
    },
    "Id": 4308,
    "Name": "Store 2",
  },
  {
    "Distance": {
      "html": "515 mi",
      "meters": 828801.866951872000,
      "miles": 514.993603999710111271424000,
    },
    "Id": 4807,
    "Name": "Store 3",
  },
  {
    "Distance": {
      "html": "516 mi",
      "meters": 828845.349454451000,
      "miles": 515.020622774168767575592000,
    },
    "Id": 1713,
    "Name": "Store 4",
  },
  {
    "Distance": {
      "html": "516 mi",
      "meters": 830159.724951922000,
      "miles": 515.837337843767915831024000,
    },
    "Id": 5354,
    "Name": "Store 5",
  }
]

When I use the [LinqToQueryable] attribute the results are returned as:

[
  {
    "Distance": {
      "html": "516 mi",
      "meters": 828845.349454451000,
      "miles": 515.020622774168767575592000,
    },
    "Id": 1713,
    "Name": "Store 4",
  },
  {
    "Distance": {
      "html": "489 mi",
      "meters": 786342.722733381000,
      "miles": 488.610714945366450160152000,
    },
    "Id": 1733,
    "Name": "Store 1",
  },
  {
    "Distance": {
      "html": "513 mi",
      "meters": 824963.241224736000,
      "miles": 512.608392555997748205312000,
    },
    "Id": 4308,
    "Name": "Store 2",
  },
  {
    "Distance": {
      "html": "515 mi",
      "meters": 828801.866951872000,
      "miles": 514.993603999710111271424000,
    },
    "Id": 4807,
    "Name": "Store 3",
  },
  {
    "Distance": {
      "html": "516 mi",
      "meters": 830159.724951922000,
      "miles": 515.837337843767915831024000,
    },
    "Id": 5354,
    "Name": "Store 5",
  }
]

Is there any way to prevent this resorting? WebApi has something like
[Queryable(EnsureStableOrdering = false)] to prevent this from happening. Do you have something similar?

If not, how can I force an $orderby on the sub-property Distance.meters? From your docs it looks like $orderby handles sub-properties, but it's not clear how to specify that in the query string.

Thanks!

Hi @roysvork , and thanks for your work.

I'm not sure this is a issue of your package or in my configuration, let's see: I have a js component which uses "advanced" OData features that are not supported directly on webApi 2, so your component was a nice discovery.

I currently have a problem that I can't solve at the moment: when the LinqToQuerystring method called at line 35 in LinqToQueryableAttribute.cs returns a Dictionary<String, Object>, the conneg.Negotiate @ line 47 always returns null (and then 500).

Is some kind of bug or (prolly) I just need to extend/add some serializer in the configuration.Formatters?

Thanks,
Antonio

Hi Pete!

I've got a requirement to add take/skip/filter on a sub-property for an resource like this:

{
  "Owner": "123",
   //  ... more properties ...
  "Activities": [
    {
      "ActivityType": "sample string 1",
       //  ... more properties ...
    },
    {
      "ActivityType": "sample string 2",
       //  ... more properties ...
    }
  ],
  "TotalCount": 2
}

Rather than add yet another parameter to the controller, is there any way to use L2QS to model bind an a filter for the activities collection? e.g.

    public MyResponse(int ownerId, Func<Activity, bool> activitiesFilter) {
        var owner = _owners.Single(o => o.OwnerId);
        owner.Activities.RemoveAll(activitiesFilter);
        return owner;
    }

It would also be handy to be able to model bind Expression<Func<Activity, bool>> so I could pass it into a Queryable.

Or maybe is this already possible by doing a projection and mapping back to the original model type?

After resolving the content negotiaton issue, I just got back to testing the feature that had me start using LinqToQueryable in the first place. It looks like $inlinecount is broken. Here are two queries using Postman. The first passes in $inlinecount. The second does not. Both are getting to my WebApi controller, but the $inlinecount version throws an exception after I return my collection. This was working fine in v0.6.4 (and still does).

ex:
?$filter=Title eq 'ThisBreaks\Avatar'
produces
"Instance property 'Avatar' is not defined for type 'LinqToQuerystring.Demo.Models.Movie'"

Great project by the way:)

As reported by Borat via email.

http://pastebin.com/RCwVLWiM

LinqToQuerystring is attempting to parse the $format system query option when the entire query string is passed. The library should only attempt to parse the system query options that it understands and ignore the other valid OData system query options.

When the $format=json system query options is passed, the following error is received:
{
"Message":"An error has occurred.",
"ExceptionMessage":"A recognition error occurred.",
"ExceptionType":"Antlr.Runtime.MismatchedTokenException",
"StackTrace":" at LinqToQuerystring.LinqToQuerystringLexer.ReportError(RecognitionException e)
at Antlr.Runtime.Lexer.NextToken() in c:\dev\stringtemplate_main\antlr\antlr3-main\runtime\CSharp3\Sources\Antlr3.Runtime\Lexer.cs:line 165
at Antlr.Runtime.BufferedTokenStream.Fetch(Int32 n) in c:\dev\stringtemplate_main\antlr\antlr3-main\runtime\CSharp3\Sources\Antlr3.Runtime\BufferedTokenStream.cs:line 228
at Antlr.Runtime.CommonTokenStream.SkipOffTokenChannels(Int32 i) in c:\dev\stringtemplate_main\antlr\antlr3-main\runtime\CSharp3\Sources\Antlr3.Runtime\CommonTokenStream.cs:line 149
at Antlr.Runtime.CommonTokenStream.Setup() in c:\dev\stringtemplate_main\antlr\antlr3-main\runtime\CSharp3\Sources\Antlr3.Runtime\CommonTokenStream.cs:line 178
at Antlr.Runtime.CommonTokenStream.LT(Int32 k) in c:\dev\stringtemplate_main\antlr\antlr3-main\runtime\CSharp3\Sources\Antlr3.Runtime\CommonTokenStream.cs:line 124
at LinqToQuerystring.LinqToQuerystringParser.prog()
at LinqToQuerystring.Extensions.LinqToQuerystring(IQueryable query, Type inputType, String queryString, Boolean forceDynamicProperties, Int32 maxPageSize)
at SampleProject.Controllers.QueryController.<>c__DisplayClass1.b__0() in c:\Users\dylan.vester\Documents\Visual Studio 2012\Projects\SampleProject\Controllers\QueryController.cs:line 42
at System.Threading.Tasks.Task`1.InnerInvoke()
at System.Threading.Tasks.Task.Execute()"
}

Can you tell me how I can get a filter with an & to work like this one?
$filter=TenantName eq 'BLIMPIE SUBS & SALADS'

Thank you.

Continuing what I had mentioned in #19 (comment) the nancy package stopped working when I updated to 0.6.8.5. I think it's due to it referencing an older build of LinqToQuerystring.

I tried adding binding redirects into my web.config but that didn't seem to do anything. In the end I copied the extension method over to my project and removed the package.

I was able to refactor the extension method a bit as well. Currently I've got

public static dynamic LinqToQuerystring<T>(this IQueryable<T> query, Uri url, bool forceDynamicProperties = false, int maxPageSize = -1)
{
    var queryString = url.Query.Replace('+', ' ');

    return query.LinqToQuerystring(typeof(T), queryString, forceDynamicProperties, maxPageSize);
}

And then I'm calling it like so

Get["/"] = _ => _db.Purchases()
                    .ForUser(CurrentUser)
                    .LinqToQuerystring(Request.Url);

if I apply a $orderBy=Name, for example and check EF Profiler this is not being applied to the Sql.

Also if I use it lower case (i.e. $orderby=Name) I get the following error:
ExceptionMessage=No method 'get_Item' exists on type xxx.

From @corneliuskopp...

We offer a service endpoint that serves several "versions" of an entity at the same time. Different customers update at different times, so between customers the "same entity" might look slightly different.

To stay multitenant-enabled, we can't really use IQueryable, as the T are different depending on the customer and hosting multiple endpoints per "version" of the entity involves overhead.

So the basic question is: Can we use LinqToQuerystring even when the controller signature is IQueryable (and not IQueryable)?

If a querystring is parsed that contains < > <= >= operators on two strings, the expression parser passes it through unchanged, which results in an error. Underneath, in SQL, strings can be compared ordinally with these operators, but EntityFramework requires them to be expressed as:

operand1.CompareTo(operand2) < 0
operand1.CompareTo(operand2) > 0
operand1.CompareTo(operand2) <= 0
operand1.CompareTo(operand2) >= 0

Unfortunately I cannot figure out how to use Configuration to replace GreaterThanNode et all with custom versions that can create these expressions. Am I correct in saying that CustomNodeMappings can't replace existing node types in this sort of way?

In any case, making it an extension isn't really important because converting these operators to CompareTo for strings is probably always going to be more useful than the error that we'd get otherwise, even if the underlying provider doesn't always have direct support for CompareTo like EF does.

Firstly, I am a fan of this project as the entity framework odata has given me many headaches. So thank you for this.

I attempted to get the package working out of nuget for my .net 4.5 web api 2 project.

But web api 2 comes with a version 5.0.0.0 of System.Web.Http which i think conflicts with the assembly in LinqToQuerystring.WebApi

From my Error Page:

Assembly Load Trace:
 The following information can be helpful to determine why the assembly 'System.Web.Http, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' could not be loaded.


=== Pre-bind state information ===
LOG: DisplayName = System.Web.Http, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
.
.
.
Calling assembly : LinqToQuerystring.WebApi, Version=1.3.1.35335, Culture=neutral, PublicKeyToken=null.

any help would be appreciated.

I just updated to your 0.6.4 release after seeing that you just fixed a "content negotiation issue". When I use the default Query attribute I get this back (from Postman):

If I use the LinqToQuery attribute I always get back:

Any ideas?

The current liLinq2toQueryString.Nancy package on nuget.org does not include support for .net 4.0, is there any reason for this? Any chance of updating it?

Hi,

I tried to install linqtoqueryable.webapi from nuget but it does not work for a project targetting 4.0

The following fails:
http://localhost/api/Services/Widgets?$orderby=Created+desc

Whereas the following succeeds:
http://localhost/api/Services/Widgets?$orderby=Created desc

Only difference is a space between the Created and desc.

I'm switching from webapi to nancy for my api calls and so far all but one of my queries has worked. The one in question is:

/api/purchases?$filter=year(PurchasedOn)+eq+2013&$inlinecount=allpages&$orderby=PurchasedOn+desc,+Id+desc&$skip=0&$top=10

The filter function year(PurchasedOn) doesn't look to be implemented. The error I'm getting is:

System.InvalidOperationException: No valid child for LinqToQuerystring.TreeNodes.FilterNode
   at LinqToQuerystring.TreeNodes.FilterNode.BuildLinqExpression(IQueryable query, Expression expression, Expression item)
   at LinqToQuerystring.Extensions.BuildQuery(TreeNode node, IQueryable& queryResult, IQueryable& constrainedQuery)
   at LinqToQuerystring.Extensions.LinqToQuerystring(IQueryable query, String queryString, Type inputType, Boolean forceDynamicProperties)

I've tried looking through the code to see if I could add this in myself but I'm not really sure how the code works so I didn't get very far.

This is what I was using as my reference when originally setting up my queries http://www.odata.org/documentation/odata-v3-documentation/url-conventions/#512412_year

The LinqToQuerystring.EntityFramework library doesn't appear to work with EF6. When attempting to use $expand, I would get the following error:

Could not load type 'System.Data.Entity.DbExtensions' from assembly 'EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.

An assembly binding redirect doesn't work in this case because the Include extension method is now a member of System.Data.Entity.QueryableExtensions. Updating the nuget package to Entity Framework 6 and recompiling corrects the issue without any code changes. See commit mpetito/LinqToQuerystring@9119808

Hello, LinqToQuerystring is a great tool. I tried to use microsoft webapi odata in my web api controllers but $inlinecount just doesn't work out of the box, you have to do a lot of hacks in order to make it works, but LinqToQuerystring solved my problems!

I was debugging the entity framework's sql querys and I found that $inlinecount doesn't generate a sql count query. I think all data are being loaded in memory and after that are being counted (too much memory!).

[6880] SELECT
[6880] [Extent1].[LogId] AS [LogId],
[6880] [Extent1].[Time] AS [Time],
[6880] [Extent1].[Event] AS [Event],
[6880] [Extent1].[UserId] AS [UserId],
[6880] [Extent1].[Message] AS [Message],
[6880] [Extent1].[IP] AS [IP]
[6880] FROM [dbo].[Log] AS [Extent1]
[6880] ORDER BY [Extent1].[LogId] DESC

I think the call should be:

SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[Log] AS [Extent1]
ORDER BY [Extent1].[LogId] DESC
) AS [GroupBy1]

Could you check it?

Any chance of adding support for group by so we can perform aggregation using the odata data aggregation extension?

http://docs.oasis-open.org/odata/odata-data-aggregation-ext/v4.0/cs01/odata-data-aggregation-ext-v4.0-cs01.html

From @wichmo

I run into an issue with multiple substringof() conditions. It only grabs the first condition.

For example api/xxxx/?$filter=(substringof('test',Name))or(substringof('test',Address))

I looked at the profile in mongo db. The #inlinecount=allpages generated a query to the collection with no limit to get the count.

When using the LinqToQueryableAttibute in the webApi2 solution any url encoded characters are not decoded:

Within LinqToQueryable.cs (27) => when the query string is pulled it is not checked for any encoding.

Then LinqToQueryable.cs (35) => this is passed to extension method LinqToQuerystring in an encoded state

Then Extensions.cs(47) => the querystring is split based upon '$' but '$' is still %24 so odataQueries count is 0

*** FIX ***

I got the code locally => added a reference to System.Web and in the LinqToQueryable.cs at line 34 did this:

var queryString = HttpUtility.UrlDecode(actionExecutedContext.Request.RequestUri.Query);

Works for both now.

I have an OData query like this:

http://localhost:41084/odata/Appointments?$expand=Person,Person/Address

This will give the following exception:

A specified Include path is not valid. The EntityType 'Site.Data.Appointment' does not declare a navigation property with the name 'Address'.

Which is correct because Appointment does not have a property Address, Person does, so it doesn't work very well with the deep includes.

I have an IQueryable and i which to filter it
but the problem when I use LinqToQuerystring I got this exeption
No method 'get_Item' exists on type 'System.Object'.

here is my code
[HttpGet]
public PageResult GetforGrid(ODataQueryOptions options, string data1)
{
try
{
DynamicTableRep dynamicTableRep = new DynamicTableRep(data1);
dynamic tbltype = dynamicTableRep.GetType();

            var dynamicQueryable = dynamicTableRep.GetAllAsQueryable();
            IQueryable<dynamic> qResult = dynamicQueryable.LinqToQuerystring(this.Request.RequestUri.Query, true);

            var results = options.ApplyTo(qResult);
            return new PageResult<dynamic>(results as IEnumerable<dynamic>,
                Request.GetNextPageLink(), Request.GetInlineCount());
        }
        catch (Exception ex)
        {
            Logs.Log(ex);
            return null;
        }
    }

When I parse my OData string with this datetime: datetime'2014-07-10T00:00:00.000'

I get an error: String was not recognized as a valid DateTime

I noticed this code in DateTimeNode.cs

        var dateText = this.Text
            .Replace("datetime'", string.Empty)
            .Replace("'", string.Empty)
            .Replace(".", ":");

The last replace makes the datetime string: 2014-07-10T00:00:00:000 but it should be 2014-07-10T00:00:00.000

When I read the OData specifications:

datetime’yyyy-mm-ddThh:mm[:ss[.fffffff]]’ NOTE: Spaces are not allowed between datetime and quoted portion. datetime is case-insensitive

I don't think there is a need to replace the . with a :, right?

I gave "Linq to Querystring" a try but found a few problems. The filters with the following syntax all throw exceptions:

$filter=toupper(Login)%20eq%20'ABC'

$filter=toupper(Supervisor/Login)%20eq%20'ABC'

$filter=Supervisor/Login%20eq%20'ABC'

Excetions look like this:

{"$id":"1","Message":"An error has occurred.","ExceptionMessage":"Object reference not set to an instance of an object.","ExceptionType":"System.NullReferenceException","StackTrace":" at System.Web.Http.ApiController.d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Tracing.Tracers.HttpControllerTracer.d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Tracing.ITraceWriterExtensions.d__21`1.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Dispatcher.HttpControllerDispatcher.d__0.MoveNext()"}

Started using this library to get the inlinecount for paginated queries in WebAPI. I have seen no issues so far except for one particular type of query. to achieve an equivalent toSQL Like "%xxxx%" I am performing the following Odata query:

/api/PersonAggregate?$filter=substringof('pete',tolower(Last)) eq true

When I do I am getting the following error.

Error:

{

"Message": "An error has occurred.",
"ExceptionMessage": "The 'ObjectContent' type failed to serialize the response body for content type 'application/json; charset=utf-8'.",
"ExceptionType": "System.InvalidOperationException",
"StackTrace": null,
"InnerException": {
    "Message": "An error has occurred.",
    "ExceptionMessage": "Object reference not set to an instance of an object.",
    "ExceptionType": "System.NullReferenceException",
    "StackTrace": " at lambda_method(Closure , PersonAggregate )\r\n at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()\r\n at System.Linq.Buffer`1..ctor(IEnumerable`1 source)\r\n at System.Linq.OrderedEnumerable`1.<GetEnumerator>d__0.MoveNext()\r\n at System.Linq.Enumerable.<TakeIterator>d__3a`1.MoveNext()\r\n at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)\r\n at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)\r\n at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType)\r\n at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType)\r\n at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)\r\n at System.Net.Http.Formatting.JsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)\r\n at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, HttpContent content)\r\n at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Tracing.ITraceWriterExtensions.<TraceBeginEndAsyncCore>d__24.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.WebHost.HttpControllerHandler.<WriteBufferedResponseContentAsync>d__19.MoveNext()"
}

}

Using .NET Framework 4.5 with WebAPI 5.0.0 underlying database is MongoDB

If I do this

var results = db.Pois.AsQueryable();
if (queryOptions.Filter != null)
r1 = await results.LinqToQuerystring(queryOptions.Filter.RawValue).ToListAsync();

then LinqToQuerystring doesn't apply the filter at all. It returns all of the results. (db is EF model)

The docs are a bit vague on what exactly are we supposed to achieve by either using LinqToQueryable attribute or LinqToQuerystring extension method.

And how does the LinqToQueryable differ from the built in Web API OData Queryable attribute?

If it's a parser it should focus more on the extension methods for manual parsing and less on attributes where the default Queryable attribute seems to work just fine.

Or am I missing some point?

I'm receiving the following exception when performing a query using the substringof function. The same error happens when trying endswith.

at LinqToQuerystring.TreeNodes.Base.TreeNode.get_ChildNodes()
at LinqToQuerystring.TreeNodes.FilterNode.BuildLinqExpression(IQueryable query, Expression expression, Expression item)
at LinqToQuerystring.Extensions.BuildQuery(TreeNode node, IQueryable& queryResult, IQueryable& constrainedQuery)
at LinqToQuerystring.Extensions.LinqToQuerystring(IQueryable query, Type inputType, String queryString, Boolean forceDynamicProperties, Int32 maxPageSize)
at LinqToQuerystring.Extensions.LinqToQuerystring[T](IQueryable`1 query, String queryString, Boolean forceDynamicProperties, Int32 maxPageSize)

The query string is:

...leads?$filter=substringof('eff', FirstName) eq true

When I use this same resource with the following query string, everything works perfectly.

...leads?$filter=FirstName eq 'jeff'

This is for a .NET 4.5 application using Entity Framework 6.1.0. The call to LinqToQuerystring is from an existing IQueryable object. Basically, I'm returning an IQueryable object for an initial query on a DbSet that has to be done regardless, then if there is a valid OData query string passed in, I then call LinqToQuerystring on that existing IQueryable.

// Returns an IQueryable to filter leads for a given member first.
var query = leadQuery.GetAvailableLeadsForMember(LoginInfoContainer.MemberInfo.WcMemberId);

// If we don't have any OData parameters, then just return the normal query.
query = string.IsNullOrEmpty(oDataQuery)
? query
: query.LinqToQuerystring(oDataQuery);

Any ideas?

This is admittedly my first attempt to use L2Q, but I am getting an unfathomable error!

I am using Web Api (1.0) and Entity Framework (5.something).

I have a relatively simple object:

public class AgentTask
{
    public int Id { get; set; }
    public Guid AgentId { get; set; }
    public Guid PropertyId { get; set; }
    public TaskType Type { get; set; }
    public TaskStatus Status { get; set; }
    public Guid CreatedBy { get; set; }
    public DateTime CreatedDate { get; set; }
    public DateTime? SlaDeadline { get; set; }
    public DateTime? UpdatedDate { get; set; }
    public virtual Property Property { get; set; }
}

In my DbContext, I have a set of these:

public IDbSet<AgentTask> AgentTasks { get; set; }

I have a service layer that runs a simple query against this set:

public IQueryable<TaskViewModel> GetIncompleteTasks(Guid agentGuid)
{
    var tasks = _commonDatabaseContext.AgentTasks
        .Include(t => t.Property)
        .Where(
            t =>
                t.AgentId == agentGuid &&
                (!t.Property.IsRemoved.HasValue || (t.Property.IsRemoved.HasValue && !t.Property.IsRemoved.Value)) &&
                (t.Status != TaskStatus.Completed && t.Status != TaskStatus.Suspended && t.Status != TaskStatus.Deleted))
        .Select(a => new TaskViewModel()
        {
            Address = a.Property.Line1 + "," + a.Property.Line2,
            Description = a.Type,
            Id = a.Id,
            PropertyId = a.PropertyId,
            SlaDeadline = a.SlaDeadline,
            UpdatedDate = a.UpdatedDate
        });

    return tasks;
}

And this is called by my controller method:

[HttpGet]
[LinqToQueryable]
public IQueryable<TaskViewModel> GetIncompleteTasks()
{
    var loggedUser = _authenticator.GetUserId();

    if (loggedUser != null)
    {
        var result = _agentTaskListService.GetIncompleteTasks(loggedUser.Value);

        return result.AsQueryable();
    }

    throw new ArgumentException("Not Found");
}

I am calling my controller using a jQuery Ajax GET request. The url ends:

GetIncompleteTasks?$top=10&$skip=0&orderby=slaDeadline%20desc&$inlinecount=allpages

I get the following exception:

{
    "message": "An error has occurred.",
    "exceptionMessage": "The action 'GetIncompleteTasks' on controller 'AgentListing' with return type 'System.Collections.Generic.Dictionary`2[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Object, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]' cannot support querying. Ensure the type of the returned content is IEnumerable, IQueryable, or a generic form of either interface.",
    "exceptionType": "System.InvalidOperationException",
    "stackTrace": "   at System.Web.Http.QueryableAttribute.ValidateReturnType(Type responseContentType, HttpActionDescriptor actionDescriptor)\r\n   at System.Web.Http.QueryableAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.CallOnActionExecuted(HttpActionContext actionContext, HttpResponseMessage response, Exception exception)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<>c__DisplayClass2.<System.Web.Http.Filters.IActionFilter.ExecuteActionFilterAsync>b__0(HttpResponseMessage response)\r\n   at System.Threading.Tasks.TaskHelpersExtensions.<>c__DisplayClass41`2.<Then>b__40(Task`1 t)\r\n   at System.Threading.Tasks.TaskHelpersExtensions.ThenImpl[TTask,TOuterResult](TTask task, Func`2 continuation, CancellationToken cancellationToken, Boolean runSynchronously)"
}

I can put a breakpoint on the line return result.AsQueryable(); in the controller method, and it hits it fine.

Any suggestions as to what I've done wrong?

If I have a list<dictionary<string, object>> containing some date fields, using the ODATA date functions (day, year, hour...) against the object (which is a DATETIME) raises the following exception:

System.Object does not support the 'year' function.

I assume this is more of the question than an issue.

I have an object that looks a little like this
Truck{ Driver[] Drivers }

in driver there is an Id field.

if I try and use "$filter=Drivers/any(d: d/Id eq '{0}')" It comes back saying that Driver[] does not have an id field.

if I run this:
foreach (var truck in trucks) { var test = truck.Drivers.Any(a => a.Id == "dddd"); }

it works fine. Also if I convert the driver array to a list LinqToQuerystring works fine.

I am trying to apply this query string to an IQueryable:

?$filter=CreateDate%20gt%20%272014-04-17%27

and I get a

"No coercion operator is defined between types 'System.String' and 'System.DateTime'."

I can't seem to find any formatting to the date that doesn't produce this, or a similar, error.

Hi, I am trying to build an Nancy OData support app using LinqToQuerystring. I got below sample code. it is working for any query url like: http:/test/?$filter=Recommendede eq true. variable tests holding right query result but i am finding difficulty in converting the result(tests) into Json response.
thanks in advance.

Get["/test"] = _ =>
var tests =
new List
{
new Movie
{
Title = "Matrix (The)",
ReleaseDate = new DateTime(1999, 3, 31),
DurationInMinutes = 136,
MetaScore = 73,
Director = "Wachowski Brothers",
Recommended = true
},
new Movie
{
Title = "Avatar",
ReleaseDate = new DateTime(2009, 12, 17),
DurationInMinutes = 162,
MetaScore = 83,
Director = "James Cameron",
Recommended = false

                             },
                     }.AsQueryable()                //.LinqToQuerystring("?$orderby=Title");
                      .LinqToQuerystring((IDictionary<string, object>)this.Context.Request.Query);

$filter parameters with 'and' or 'or' are being parsed as Expression.And instead of Expression.AndAlso and Expression.Or instead of Expression.OrElse.

This causes issues with some linq providers. The simple fix is to change Expression.And to Expression.AndAlso in TreeNodes/AndNode.cs line 20, and Expression.Or to Expression.OrElse in TreeNodes.OrNode.cs line 20.

Please let me know if I can provide anymore information.

let's say this is my query string:

$top=10&$skip=0&$orderBy=CreateDate desc

Everything works great

If I change it to this:

$skip=0&$orderBy=CreateDate desc&$top=10

Then the top doesn't appear to be applied to my results. In this case I'm using this against a queryable exposed by the MongoDB client API. With the second query, shifting the $top to the end of the query, the result set doesn't get limited.

this also occurs if a MaxPageSize is added to the extension method, as this moves the $top to the end of the list.