Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> How to get the row number/index through LInQ?
 

Pages: 1
LLBLGen Pro Runtime Framework
How to get the row number/index through LInQ?
Page:1/1 

  Print all messages in this thread  
Poster Message
eSPiYa
User



Location:
Makati, Philippines
Joined on:
12-Oct-2018 04:47:52
Posted:
10 posts
# Posted on: 11-Nov-2019 06:41:17.  
This one looks like not working
Code:
var metaData = new LinqMetaData(adapter);
metaData.VwInventory.Select((value, index) => new { index, value });


I'm receiving this error message:
Code:
base: {"variable 'index' of type 'System.Int32' referenced from scope '', but it is not defined"}


We're using version 4.2, I believe.
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8088 posts
# Posted on: 11-Nov-2019 09:12:14.  
You can't use it in this way, as there is no function mapping that you write in your linq query that maps into a DB function that return the row index.

What you can do is fetch the results normally, then use linq (in-memory) tricks: https://stackoverflow.com/questions/2471588/how-to-get-index-using-linq

Example:
Code:
var results = metaData.VwInventory.ToList();
var orderedList = results .Select((v, i) => new {inventoryItem = v, index = i}).


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
eSPiYa
User



Location:
Makati, Philippines
Joined on:
12-Oct-2018 04:47:52
Posted:
10 posts
# Posted on: 12-Nov-2019 03:11:12.  
daelmo wrote:
You can't use it in this way, as there is no function mapping that you write in your linq query that maps into a DB function that return the row index.

What you can do is fetch the results normally, then use linq (in-memory) tricks: https://stackoverflow.com/questions/2471588/how-to-get-index-using-linq

Example:
Code:
var results = metaData.VwInventory.ToList();
var orderedList = results .Select((v, i) => new {inventoryItem = v, index = i}).

That would be a problem, one of our clients have tens of thousands of records in that view alone.

I just need to get the index/row number of certain records. Something like this:
Code:
SELECT IndexedInv.IDX
FROM
(SELECT TOP(1000) ROW_NUMBER() OVER(order by itemnumber) AS IDX, InventoryId
FROM VwInventory) AS IndexedInv
WHERE IndexedInv.InventoryId in (30351, 30402, 30421)


How about getting the datareader from the Linq? Will it be faster and how to do it? Thanks!
  Top
eSPiYa
User



Location:
Makati, Philippines
Joined on:
12-Oct-2018 04:47:52
Posted:
10 posts
# Posted on: 12-Nov-2019 03:23:16.  
daelmo wrote:
You can't use it in this way, as there is no function mapping that you write in your linq query that maps into a DB function that return the row index.

And I tried to search about this function mapping and found this https://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/Linq/gencode_linq_functionmappings.htm

But how can I map SQL Server's "Row_Number()" function to LInQ's "index in
Code:
.Select((value, index) > new {value, index})
?


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37642 posts
# Posted on: 12-Nov-2019 11:01:29.  
Linq can't offer that functionality for that query. You can use paging though, or use '.Take(1000)' to fetch the first 1000 rows. The 'index' in the resultset is a concept that's the same as indexing the resultset you got after the fact. Index has no meaning otherwise: if you have a table, and you apply a where clause on a select on that table, the 'index' of a given row is something you can't say anything about, as a select has no ordering by default.

What is it that you want to do with the index values that you get back with the query you gave in your previous post?

You could create a queryspec query with a DbFunctionCall using "ROW_NUMBER() OVER (order by {0})", and use that, however I think if you elaborate a bit why you want to have the indexes, we might be able to give a better query that does what you want to achieve.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
eSPiYa
User



Location:
Makati, Philippines
Joined on:
12-Oct-2018 04:47:52
Posted:
10 posts
# Posted on: 13-Nov-2019 06:26:59.  
Otis wrote:
Linq can't offer that functionality for that query. You can use paging though, or use '.Take(1000)' to fetch the first 1000 rows. The 'index' in the resultset is a concept that's the same as indexing the resultset you got after the fact. Index has no meaning otherwise: if you have a table, and you apply a where clause on a select on that table, the 'index' of a given row is something you can't say anything about, as a select has no ordering by default.

What is it that you want to do with the index values that you get back with the query you gave in your previous post?

You could create a queryspec query with a DbFunctionCall using "ROW_NUMBER() OVER (order by {0})", and use that, however I think if you elaborate a bit why you want to have the indexes, we might be able to give a better query that does what you want to achieve.

I'm avoiding QuerySpec and want to use things that are mostly native to .NET, it is easier to remove dependencies on LLBLGen. Truthfully, I prefer to use open source ORMs but our team leader is a big fan of LLBLGen, and we're having lots of issues because of it.

Anyway, we have a front-end control(another non-open source product) that requires to return the index/row_number of the items by id/pk after applying some filters.

examples:
This one will fetch the index(RowNum) with additional fields(InvetoryId, ItemNumber, ItemName) of the records with InventoryId 11034, 25, and 33
Code:
SELECT *
FROM(
SELECT ROW_NUMBER() OVER(ORDER BY ItemNumber ASC) as RowNum, InventoryId, ItemNumber, ItemName
FROM VwInventory
ORDER BY ItemNumber ASC) AS Invs
WHERE Invs.InventoryId in
(
11034,
25,
33
)


This one is same with above but additional filter(starts with string "rap"), and the index I'm capturing here is of records with InventoryId of 15 and 16.
Code:
SELECT *
FROM(
SELECT ROW_NUMBER() OVER(ORDER BY ItemNumber ASC) as RowNum, InventoryId, ItemNumber, ItemName
FROM VwInventory
WHERE ItemName like 'Rap%'
ORDER BY ItemNumber ASC) AS Invs
WHERE Invs.InventoryId in
(
15,
16
)


Identical to above with different filtering and Order By.
Code:
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ItemName ASC) as RowNum, InventoryId, ItemNumber, ItemName
FROM VwInventory
WHERE ItemName like 'Brace%'
ORDER BY ItemName ASC) AS Invs
WHERE Invs.InventoryId in
(
11490,
11495,
11541
)


We just need to get the index of certain records after applying filters and sorts.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37642 posts
# Posted on: 13-Nov-2019 09:15:36.  
eSPiYa wrote:
Otis wrote:
Linq can't offer that functionality for that query. You can use paging though, or use '.Take(1000)' to fetch the first 1000 rows. The 'index' in the resultset is a concept that's the same as indexing the resultset you got after the fact. Index has no meaning otherwise: if you have a table, and you apply a where clause on a select on that table, the 'index' of a given row is something you can't say anything about, as a select has no ordering by default.

What is it that you want to do with the index values that you get back with the query you gave in your previous post?

You could create a queryspec query with a DbFunctionCall using "ROW_NUMBER() OVER (order by {0})", and use that, however I think if you elaborate a bit why you want to have the indexes, we might be able to give a better query that does what you want to achieve.

I'm avoiding QuerySpec and want to use things that are mostly native to .NET, it is easier to remove dependencies on LLBLGen. Truthfully, I prefer to use open source ORMs but our team leader is a big fan of LLBLGen, and we're having lots of issues because of it.

And what issues might that be? I see you posting here a question that no ORM can solve in their linq provider simply because it's not a supported feature of what Linq (the Linq -> expression tree) can give you. Not without a custom method anyway. Btw v4.2 is 4+ years old. Our linq provider and runtime has been updated a lot since then, perhaps a lot of these issues you're talking about stem from an aspect we solved in the v5 versions?

If there are 'many issues', it would be great if you could ask us what to do to solve it, so you have less problems. Queryspec is a query system using a fluent API because Linq is too rigid and limited in its API and it's not always possible to determine what SQL it will generate. I'm not sure why you're limiting yourself so much. Most ORMs out there have at least one custom query API besides Linq, and for good reason. And with Linq it's not as if you can swap out one ORM for another (if that's even possible anyway).

What 'open source' ORM do you want to use instead? EF Core? Laugh Good luck with that.

Look, I've heard this sentiment occasionally over the past 17 years I now work on LLBLGen Pro, and trust me, you're doing yourself a disservice by limiting yourself because of some 'what if' scenario where you have to swap out the ORM. Using an ORM, even your own, is a dependency you have to live with, as with all dependencies taken in an app. Swapping out one for the other is always going to hurt, no matter what you do. But thing is: people seldomly do that. It's not as if our framework is too limited that you *need* to swap it for something else because otherwise you can't solve your client's problems. It might not be 'open source', that's right. Not sure why having an open source license is a key aspect of good software tho.

Besides, every licensee has the sourcecode available to them. You can change it if you want, same rules apply as with OSS.

Quote:

Anyway, we have a front-end control(another non-open source product) that requires to return the index/row_number of the items by id/pk after applying some filters.

examples:
This one will fetch the index(RowNum) with additional fields(InvetoryId, ItemNumber, ItemName) of the records with InventoryId 11034, 25, and 33
Code:
SELECT *
FROM(
SELECT ROW_NUMBER() OVER(ORDER BY ItemNumber ASC) as RowNum, InventoryId, ItemNumber, ItemName
FROM VwInventory
ORDER BY ItemNumber ASC) AS Invs
WHERE Invs.InventoryId in
(
11034,
25,
33
)


This one is same with above but additional filter(starts with string "rap"), and the index I'm capturing here is of records with InventoryId of 15 and 16.
Code:
SELECT *
FROM(
SELECT ROW_NUMBER() OVER(ORDER BY ItemNumber ASC) as RowNum, InventoryId, ItemNumber, ItemName
FROM VwInventory
WHERE ItemName like 'Rap%'
ORDER BY ItemNumber ASC) AS Invs
WHERE Invs.InventoryId in
(
15,
16
)


Identical to above with different filtering and Order By.
Code:
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ItemName ASC) as RowNum, InventoryId, ItemNumber, ItemName
FROM VwInventory
WHERE ItemName like 'Brace%'
ORDER BY ItemName ASC) AS Invs
WHERE Invs.InventoryId in
(
11490,
11495,
11541
)


We just need to get the index of certain records after applying filters and sorts.

Yes, and I have given you a way to do that. There's no way in **linq** to do this, as Linq itself (the Linq -> expression tree system, which every ORM with a linq provider has to work with) can't give you that: the 'ROW_NUMBER() OVER ()' construct needed to give the indices isn't something you can generate, as Linq works with typed elements and you can't generate things out of thin air. You could define a custom function call and use it in a linq projection but that might not be what you want, as it isn't 'native .NET'.

So, not sure what you want, as the solution is something you don't want to use, and what you want to use can't give you this (not our fault, but the way linq expression trees work).
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
eSPiYa
User



Location:
Makati, Philippines
Joined on:
12-Oct-2018 04:47:52
Posted:
10 posts
# Posted on: 13-Nov-2019 09:44:24.  
Otis wrote:
And what issues might that be? I see you posting here a question that no ORM can solve in their linq provider simply because it's not a supported feature of what Linq (the Linq -> expression tree) can give you. Not without a custom method anyway. Btw v4.2 is 4+ years old. Our linq provider and runtime has been updated a lot since then.

I believe our license is for that version only. The project owner refuses to renew our license because he says it is too expensive. Well, that's a major issue, fixes and new features are locked behind paywall which is there's nothing anyone can do about it. And I believe we can't contribute like I usually can do on open source projects.

Otis wrote:
Yes, and I have given you a way to do that. There's no way in **linq** to do this, as Linq itself (the Linq -> expression tree system, which every ORM with a linq provider has to work with) can't give you that: the 'ROW_NUMBER() OVER ()' construct needed to give the indices isn't something you can generate, as Linq works with typed elements and you can't generate things out of thin air. You could define a custom function call and use it in a linq projection but that might not be what you want, as it isn't 'native .NET'.

The "index" part, I used it before with Entity Framework with PostgreSQL and it is working fine out of the box. It is using the same function/expression as SQL Server that is why I believe it should be doable with your ORM.

Anyway, is there a way to map that "index" to the row_number of LLBLGen instead of resorting to using QuerySpecs?

I'm sorry for my words, I just can't get my self to like things that are hard to extend. Open source are more convenient because if there are bugs that I found or things that I can extend, I'll just do it and submit my codes. Some of my codes were accepted within the day, some are scrapped but they change my implementation to something else which is still convenient because I can still get what I want. I have a lot of dissatisfaction with our team leader's choice of tools, and there are lots of hardships because of it. We tend to make lots of workarounds just to achieve what we need to do, which can easily be finished using other tools in a shorter time.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37642 posts
# Posted on: 13-Nov-2019 11:04:06.  
eSPiYa wrote:
Otis wrote:
And what issues might that be? I see you posting here a question that no ORM can solve in their linq provider simply because it's not a supported feature of what Linq (the Linq -> expression tree) can give you. Not without a custom method anyway. Btw v4.2 is 4+ years old. Our linq provider and runtime has been updated a lot since then.

I believe our license is for that version only. The project owner refuses to renew our license because he says it is too expensive. Well, that's a major issue, fixes and new features are locked behind paywall which is there's nothing anyone can do about it. And I believe we can't contribute like I usually can do on open source projects.

That I understand, and we're not as the usual tech companies that you won't get support anymore because your license is e.g. a year old. I mean, it's a 4+ year old version but you still get support. We won't fix things anymore in 4 year old software, which is I think understandable. You too don't work for free, right? We don't either. Wink

Yeah you can't 'contribute' in the usual fashion like sending a PR, but frankly orm runtimes aren't simple. Contributing to an ORM runtime takes serious time. That said, we have accepted contributions in the past (as you have the sourcecode after all): parameterized prefetch paths for instance, or the binary serialization code to do very fast, compact binary serialization are external contributions.

Otis wrote:
Yes, and I have given you a way to do that. There's no way in **linq** to do this, as Linq itself (the Linq -> expression tree system, which every ORM with a linq provider has to work with) can't give you that: the 'ROW_NUMBER() OVER ()' construct needed to give the indices isn't something you can generate, as Linq works with typed elements and you can't generate things out of thin air. You could define a custom function call and use it in a linq projection but that might not be what you want, as it isn't 'native .NET'.

The "index" part, I used it before with Entity Framework with PostgreSQL and it is working fine out of the box. It is using the same function/expression as SQL Server that is why I believe it should be doable with your ORM.

I think you mean paging over a resultset (which is supported) ? EF doesn't return an index in the resultset as well... The linq query you mentioned in the startpost, you're saying that one works on EF? As this SO question suggest it doesn't either:
https://stackoverflow.com/questions/1165028/how-do-i-add-row-number-to-a-linq-query-or-entity

We support paging using e.g. ROW_NUMBER() but we don't emit the row number value in the resultset. the reason is that this is irrelevant:

Say you want to read page 5 (first page is page 1), and each page is 12 rows. you could do:
var page5 = metaData.Customers.OrderBy(c=>c.CustomerID).Take(12).Skip(4*12).ToList();

The index of the first element in the 'page5' list is 4*12 + 1.
Using SQL Server 2005 or higher compatibility level setting (in v4.2 you have to explicitly set this to 2005+), you'll get a row_number() over () using query on sqlserver.

The SO question above switches to AsEnumerable. I tried it on EF 6.3:
Code:

[Test]
public void SelectWithIndexTest()
{
    var q = CreateContext().Customers.OrderBy(c => c.CustomerId).Skip(4 * 12).Take(12).Select((c, index) => new {Customer = c, index = index + (4 * 12)});
    var results = q.ToList();
}

gives:
Code:

Tester.SimpleDTOTestsCodeFirst.SelectWithIndexTest

System.NotSupportedException : LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[<>f__AnonymousType0`2[NW26CF.EntityClasses.Customer,System.Int32]] Select[Customer,<>f__AnonymousType0`2](System.Linq.IQueryable`1[NW26CF.EntityClasses.Customer], System.Linq.Expressions.Expression`1[System.Func`3[NW26CF.EntityClasses.Customer,System.Int32,<>f__AnonymousType0`2[NW26CF.EntityClasses.Customer,System.Int32]]])' method, and this method cannot be translated into a store expression.


If I switch to AsEnumerable():
Code:

SELECT
    [Project1].[CustomerID] AS [CustomerID],
    [Project1].[CompanyName] AS [CompanyName],
    [Project1].[ContactName] AS [ContactName],
    [Project1].[ContactTitle] AS [ContactTitle],
    [Project1].[Fax] AS [Fax],
    [Project1].[Phone] AS [Phone],
    [Project1].[PostalCode] AS [PostalCode],
    [Project1].[C1] AS [C1],
    [Project1].[Address] AS [Address],
    [Project1].[City] AS [City],
    [Project1].[Country] AS [Country]    
FROM
    ( SELECT
        [Extent1].[CustomerID] AS [CustomerID],
        [Extent1].[CompanyName] AS [CompanyName],
        [Extent1].[ContactName] AS [ContactName],
        [Extent1].[ContactTitle] AS [ContactTitle],
        [Extent1].[Fax] AS [Fax],
        [Extent1].[Phone] AS [Phone],
        [Extent1].[PostalCode] AS [PostalCode],
        [Extent1].[Address] AS [Address],
        [Extent1].[City] AS [City],
        [Extent1].[Country] AS [Country],
        1 AS [C1]        
    FROM
        [dbo].[Customers] AS [Extent1]     ) AS [Project1]    
ORDER BY
    [Project1].[CustomerID] ASC     OFFSET 48 ROWS FETCH NEXT 12 ROWS ONLY

Which is an ordinary paging query which is doable with our runtime as well (same linq syntax), the index retrieval is done in-memory: it first fetches the page with the entities, then projects them in-memory with the index value.

Quote:

Anyway, is there a way to map that "index" to the row_number of LLBLGen instead of resorting to using QuerySpecs?

I think only with a function mapping. I'll see if I can create one for you and get back to you.

Quote:
I'm sorry for my words, I just can't get my self to like things that are hard to extend. Open source are more convenient because if there are bugs that I found or things that I can extend, I'll just do it and submit my codes. Some of my codes were accepted within the day, some are scrapped but they change my implementation to something else which is still convenient because I can still get what I want. I have a lot of dissatisfaction with our team leader's choice of tools, and there are lots of hardships because of it. We tend to make lots of workarounds just to achieve what we need to do, which can easily be finished using other tools in a shorter time.

It's ok, we've all been there Regular Smiley
The main think I've learned tho is that switching to something else gives perhaps easier things in one area but you have to work around other things elsewhere. I won't say my work is perfect, and 4.2 definitely had quirks, but I know other orms aren't either (not by a long shot). With our stuff it's a matter of: just accept how it works and use it the way it is intended. We added a lot of extension points so if you want to change something and have no idea how to do it, just ask, there's likely a way to do it with little code.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37642 posts
# Posted on: 13-Nov-2019 11:31:45.  
Ok, got it working. My example is on Northwind, but you can easily adopt it to your own db.

First you need to define a small class that contains your function which will be needed to satisfy the C# compiler:

Code:

public class NorthwindFunctions
{
    public static int IndexOfElement(object field)
    {
        return 0;
    }
}


Then you have to define the function mapping, which will be used to convert a function call into a sql fragment:

Code:

public class NorthwindFunctionMappings : FunctionMappingStore
{
    /// <summary>
    /// Initializes a new instance of the <see cref="NorthwindFunctionMappings"/> class.
    /// </summary>
    public NorthwindFunctionMappings()
        : base()
    {
        // ROW_NUMBER() OVER (ORDER BY field)
        this.Add(new FunctionMapping(typeof(NorthwindFunctions), "IndexOfElement", 1, "ROW_NUMBER() OVER(ORDER BY {0})"));
    }
}


And that's basically it. The linq query then becomes:
Code:

using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter, new NorthwindFunctionMappings());
    var q1 = metaData.Order.OrderBy(o => o.OrderId).Take(1000)
                    .Select(o => new {Idx = NorthwindFunctions.IndexOfElement(o.OrderId), OrderId = o.OrderId});
    var q = q1.Where(o => new List<int>() {10254, 10353, 10341}.Contains(o.OrderId)).Select(o => o.Idx);
    var results = q.ToList();
    Assert.AreEqual(3, results.Count);
}

Pay attention to pass an instance of the custom function mapping class to the LinqMetaData ctor.

This then becomes in sql:
Code:

SELECT [LPA_L1].[Idx]
FROM (SELECT TOP(@p2) ROW_NUMBER() OVER(ORDER BY [LPLA_1].[OrderID]) AS [Idx],
                        [LPLA_1].[OrderID]                             AS [OrderId]
        FROM [Northwind].[dbo].[Orders] [LPLA_1]
        ORDER BY [LPLA_1].[OrderID] ASC) [LPA_L1]
WHERE ((((([LPA_L1].[OrderId] IN (@p4, @p6, @p8))))))

Which is what you were after I think Regular Smiley

(gives as results: 7, 94, 106)

(this is on 5.6, but I am sure it also works on 4.2)


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
eSPiYa
User



Location:
Makati, Philippines
Joined on:
12-Oct-2018 04:47:52
Posted:
10 posts
# Posted on: 14-Nov-2019 01:13:21.  
Thanks! Will try this out. Hope this would work on 4.2. Will come back if it worked or not. Thanks again!
  Top
eSPiYa
User



Location:
Makati, Philippines
Joined on:
12-Oct-2018 04:47:52
Posted:
10 posts
# Posted on: 14-Nov-2019 04:39:18.  
Otis wrote:
Ok, got it working. My example is on Northwind, but you can easily adopt it to your own db.

First you need to define a small class that contains your function which will be needed to satisfy the C# compiler:

Code:

public class NorthwindFunctions
{
    public static int IndexOfElement(object field)
    {
        return 0;
    }
}


Then you have to define the function mapping, which will be used to convert a function call into a sql fragment:

Code:

public class NorthwindFunctionMappings : FunctionMappingStore
{
    /// <summary>
    /// Initializes a new instance of the <see cref="NorthwindFunctionMappings"/> class.
    /// </summary>
    public NorthwindFunctionMappings()
        : base()
    {
        // ROW_NUMBER() OVER (ORDER BY field)
        this.Add(new FunctionMapping(typeof(NorthwindFunctions), "IndexOfElement", 1, "ROW_NUMBER() OVER(ORDER BY {0})"));
    }
}


And that's basically it. The linq query then becomes:
Code:

using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter, new NorthwindFunctionMappings());
    var q1 = metaData.Order.OrderBy(o => o.OrderId).Take(1000)
                    .Select(o => new {Idx = NorthwindFunctions.IndexOfElement(o.OrderId), OrderId = o.OrderId});
    var q = q1.Where(o => new List<int>() {10254, 10353, 10341}.Contains(o.OrderId)).Select(o => o.Idx);
    var results = q.ToList();
    Assert.AreEqual(3, results.Count);
}

Pay attention to pass an instance of the custom function mapping class to the LinqMetaData ctor.

This then becomes in sql:
Code:

SELECT [LPA_L1].[Idx]
FROM (SELECT TOP(@p2) ROW_NUMBER() OVER(ORDER BY [LPLA_1].[OrderID]) AS [Idx],
                        [LPLA_1].[OrderID]                             AS [OrderId]
        FROM [Northwind].[dbo].[Orders] [LPLA_1]
        ORDER BY [LPLA_1].[OrderID] ASC) [LPA_L1]
WHERE ((((([LPA_L1].[OrderId] IN (@p4, @p6, @p8))))))

Which is what you were after I think Regular Smiley

(gives as results: 7, 94, 106)

(this is on 5.6, but I am sure it also works on 4.2)

I tried and this worked on 4.2. But this one is limited only on single field with ascending direction.

I'm now trying to apply the default sort and one with dynamic number of parameters. Is there a way to make an overload for the IndexOfElement, one without parameters and the other one with IEnumerable of a custom class SortDescriptor with properties Member and SortDirection?

The parameterless will use "ROW_NUMBER() OVER(ORDER BY (SELECT NULL))", while the other one will be built from the list.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37642 posts
# Posted on: 14-Nov-2019 21:29:30.  
The function mapping system works with a fixed set of parameters (see docs: https://www.llblgen.com/Documentation/5.5/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Linq/gencode_linq_functionmappings.htm is the same on 4.2)

So you can't pass in a set of parameters of variable length. You could define a couple of overloads though. I doubt you have to sort on more than 3-4 fields (as the sorting likely won't change anyway).

The problem with no field in the function mapping is that it's then not tied to a DB element, and it's then seen as an in-memory call. Linq queries mix in-memory code with code targeting the DB, so to distinguish between the two we do a visit check over the tree to see if a DB originating element is used in an expression. If not, the expression is seen as in-memory and is compiled to native code. See this to overcome that: https://www.llblgen.com/Documentation/5.5/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Linq/gencode_linq_functionmappings.htm#mapping-a-function-with-no-arguments
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.