-1

I'm setting a CreateFilteredQuery with IQueryable Interface. I have to return an IQueryable value but I'm getting IOrderedQueryable through an OrderBy function. Is there any way to copy all sensible data (not format from order and those unnecesary data) with that order into an IQueryable one?

I have tried almost everything. I can't change the return value because the functions is also for another "filters".

I'm trying to sort a column that is originally a String Column but is filled with numbers, so I have to parse into Int and then, orderby. (If I don't do that, if I order the result will be like: 1 2 20 22 3 instead of 1 2 3 20 22)

I created "tareas" with base.CreateFilteredQuery(input), and I'm trying to order by externalId before parsing to int

if (input.Sorting == "externalId asc")
{
    var tareasOrdenadas = (tareas.ToList()).OrderBy(t => int.Parse(t.ExternalId));
    return tareasOrdenadas;
}

I expect the output of System.Data.Entity.DbSet or IQueryable. Until this moment I had System.Linq.OrdenedEnumerable or just simply IOrderedEnumerable

PD: When I modify "tareas" in other filters, I have a "System.Data.Entity.Infrastructure.DbQuery value for System.Linq.IQueryable type"

I need an IQueryable type, not an IOrderedEnumerable, AsQueryable() doesn't works

PD2: Thanks you all for the help. Sorry about no replies, I was out of the office for a few days. I'll try all you give to me, thanks you all.

Happy Coding

sactrek
  • 1
  • 3
  • 1
    I don't understand your question. Please [edit] and include [mcve]. – Aleks Andreev Aug 13 '19 at 12:13
  • 1
    This code will read *everything* from the table behind `tareas` in memory and try to sort it locally. Why don't you use just `tareas.OrderBy(t->t.ExtenralId)` instead? If `ExternalId` is a string instead of a number, it's a bad bug that needs fixing. `01` and `1` as numbers are identical while the *strings* are completely different – Panagiotis Kanavos Aug 13 '19 at 12:28
  • Is there a reason ExternalId isn't an int type in the database schema already? If all ExternalIds can be parsed, fix the problem at the level your schema, then in your EF model, and then you won't have to do a ToList() or a Parse(), and this whole problem will go away. – StriplingWarrior Aug 13 '19 at 13:26
  • Why is `ExternalId` a string? This would allow duplicate numbers like `001`, `01` and `1`. And if that's acceptable, it makes no sense to use *numeric* sorting in SQL. All those IDs would appear in random order, when SQL would apply the proper alphabetic order according ie `001`, `01`, `1` – Panagiotis Kanavos Aug 13 '19 at 13:27
  • The actual problem is the sorting. If you sort by length and then by the string value you'll have the equivalent of numeric sorting (if there are no leading zeros) and you can return `IQueryable`. – Gert Arnold Aug 13 '19 at 13:34
  • Just a tip. It would help, not us, but yourself, if you'd be a bit more responsive. Now your question sinks into oblivion. – Gert Arnold Aug 15 '19 at 15:00

3 Answers3

1

You can call .AsQueryable() on an IEnumerable sequence and it will return IQueryable

Bruno Belmondo
  • 2,299
  • 8
  • 18
  • 1
    While this would allow compilation, it would only cover up the error intoduced by `ToList()`. There's no queryable anymore, the entire table is already loaded in memory. – Panagiotis Kanavos Aug 13 '19 at 12:29
  • 1
    I thought that the question was exactly to fix the compilation issue. Collecting data prior or after the query depends roughly on the context and I was not able to determine if removing the ToList() was a good answer or not. – Bruno Belmondo Aug 13 '19 at 12:35
  • 1
    That's the other thing: [IOrderedQueryable](https://learn.microsoft.com/en-us/dotnet/api/system.linq.iorderedqueryable-1?view=netframework-4.8) inherits from `IQueryable`. There shouldn't be any compilation issue. If there was people wouldn't be able to use any LINQ operators after `OrderBy`. I suspect the OP is mixing up IOrderedEnumerable and IOrderedQueryable – Panagiotis Kanavos Aug 13 '19 at 13:11
  • OrderBY on a List returns a IOrderedEnumerable not IOrderedQueryable. The given example is legit and does not compile. – Bruno Belmondo Aug 13 '19 at 13:13
  • I'm talking about the question itself, which asks how to convert `Convert IQueryable from IOrderedQueryable`. I suspect the OP has mixed up enumerables and queries – Panagiotis Kanavos Aug 13 '19 at 13:14
  • I'll take responsibility for the first down-vote, because the point of Stack Overflow is not merely to get a technically-correct answer to the question, as asked, but to actually help people out. If someone asks why their gun isn't working, do you simply show them how to take the safety off, or do you ask a few questions to make sure they won't shoot themselves in the foot first? – StriplingWarrior Aug 13 '19 at 13:23
  • @StriplingWarrior I prefer commenting over downvoting these days. Besides, the OP still hasn't answered why `ExternalId` is a string. – Panagiotis Kanavos Aug 13 '19 at 13:25
  • I'll typically down-vote an answer that I consider dangerous immediately in hopes the OP doesn't try it, but I will also follow up the down-vote with a comment. – StriplingWarrior Aug 13 '19 at 13:29
  • Hi All, ExternalId is a string in this proyect, is not my "fault", the string is used in many others classes, this is the only one case where I need to parse into an int (because I have to order by) so I don't want to change everything, just where I need it – sactrek Aug 14 '19 at 06:07
0

You can write a db function in database to convert string to integer(because there isn't any built-in functionality for this) and use that function in you linq query convert that string value to integer when you are ordering. In that way you don't have to call .ToList() method

Something like this

if (input.Sorting == "externalId asc")
{
   // you can easily return IOrderedQueryable as IQueryable
   retrun  tareas.OrderBy(t => context.YourDBFunction(t.ExternalId));
}
Wamiq Rehman
  • 566
  • 3
  • 11
0

I think your root problem is requiring an IQueryable return type. I assume the purpose is to enable adding additional database-level operations (sorting, filtering) onto the base query. The problem here is that IQueryable providers support a limited set of operations, so you often have to escape the database layer (I would use AsEnumerable() for this instead of ToList(), BTW) to apply in-memory operations. Once you do that, there's no way to go back to the database layer.

If you change your expected return type to IEnumerable you should lose the compilation errors.

Another option is to stay in the database layer by using Convert.ToInt32 instead of int.Parse:

if (input.Sorting == "externalId asc")
{
    var tareasOrdenadas = tareas.OrderBy(t => Convert.ToInt32(t.ExternalId));
    return tareasOrdenadas;
}
D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • Don't you think this will raise this error "LINQ to Entities does not recognize the method 'Int32 ToInt32(System.Object)" ? – Wamiq Rehman Aug 13 '19 at 16:02
  • @WamiqRehman From what I've read EF5 and higher will turn that into a CAST expression in SQL, but yes it is possible that it will suffer the same problem as `int.Parse`. – D Stanley Aug 13 '19 at 16:04
  • EF-core converts this into `CONVERT(int, [ColumnName])`, EF6 doesn't. Which is sad, even LINQ-to-SQL does it. – Gert Arnold Aug 14 '19 at 08:25