none
How can I select a certain max number of records using the sql-like LINQ syntax?

    Question


  • I want to select a specified count of records. IOW, I want this:

    int countToFetch = 42;
    . . .
    from i in inventoryItems
    where 0 < String.Compare(i.Id, ID)
    select i;


    ...to be someting like:

    int countToFetch = 42;
    . . .
    from i in inventoryItems
    where 0 < String.Compare(i.Id, ID)
    select i
    take countToFetch;


    I see that can be done using the other ("Lambda"?) style of LINQ syntax, but how about with the to-me preferred sql-like syntax?
    Tuesday, November 12, 2013 9:38 PM

Answers

  • Hello,

    >>but how about with the to-me preferred sql-like syntax?

    What does it mean? If it means that you want to use LINQ syntax which equals sql syntax like below(I assume that you use SQLServer):

    Select top 10 Foo from MyTables

    However, the ‘top’ syntax is just mapped to the method ‘take()’.

    Or you want to use codes like below:

    int countFetch = 2;
    
                    int i = 0;
    
                    var restult = (from order in db.Orders
    
                                   where i++ < countFetch
    
                                   select order);
    

    This is impossible, the expression tree cannot contain an assignment operator like ‘++’ and others.

    So ‘take()’ is the only way for us the select a specified count of records.

    If I have misunderstood, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 13, 2013 2:02 AM
    Moderator

All replies

  • Hello,

    >>but how about with the to-me preferred sql-like syntax?

    What does it mean? If it means that you want to use LINQ syntax which equals sql syntax like below(I assume that you use SQLServer):

    Select top 10 Foo from MyTables

    However, the ‘top’ syntax is just mapped to the method ‘take()’.

    Or you want to use codes like below:

    int countFetch = 2;
    
                    int i = 0;
    
                    var restult = (from order in db.Orders
    
                                   where i++ < countFetch
    
                                   select order);
    

    This is impossible, the expression tree cannot contain an assignment operator like ‘++’ and others.

    So ‘take()’ is the only way for us the select a specified count of records.

    If I have misunderstood, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 13, 2013 2:02 AM
    Moderator
  • "I assume that you use SQLServer"

    No, this is a LINQ-to-objects situation; I'm querying instances of a custom class.

    "So ‘take()’ is the only way for us the select a specified count of records."

    Yes, I ended up using:

    IEnumerable<InventoryItem> BatchOfInventoryItems = inventoryItems.Where(i => 0 < String.Compare(i.Id, ID)).Take(CountToFetch);

    ...but that method is not being called for some reason (see http://forums.asp.net/p/1949612/5560120.aspx?p=True&t=635199405973264072&pagenum=1)

    A more extensive discussion of this dilemma can be mused upon bemusedly here: http://stackoverflow.com/questions/19963238/why-is-my-web-api-routing-being-re-routed-falsely-routed/19963487?noredirect=1#comment29718523_19963487

    As can be seen there, the problem turned out to be that I started off the ID argument (which is a string, notwithstanding its name) with string.Empty and then a blank string (" ") assigned to it; for whatever reason, this was apparently causing it to fail...
    Wednesday, November 13, 2013 5:18 PM
  • Hi,

    Since WebAPI call is a MVC issue, I will involve MVC experts to help you to work with this issue.

    As soon as they have any result, I will report it.

    Or you can ask this issue to the MVC forum:

    http://forums.asp.net/1146.aspx

    Thanks for your understanding.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 14, 2013 10:35 AM
    Moderator