locked
C# Question: LINQ Select statement all records .... RRS feed

  • Question

  • User-507786106 posted
    I have a LINQ SELECT statement Question:
    I need to Select from TableA with 2 where clauses,
    Order by and get the count of Number of records returned.

    Goal: one statement that select 2 fields from the
    record with a count of how many records selected

    var data = ( from x in dB.TableA.....

    Results
    Data.Count
    Data.Id
    Data.DueDate

    Is it possible to make one call to the database and
    get fields with row count?

    Thanks for sharing your expertise
    Sunday, June 3, 2018 1:21 AM

Answers

  • User283571144 posted

    Hi slimbunny,

    Is it possible to make one call to the database and
    get fields with row count?

    As far as I know, we couldn't directly get the selected count from Linq with orderby.

    I suggest you could firstly get the list of model by using the Linq.

    Then we could use List.Count method to get the result's count.

    It will just make one call to database.

    More details, you could refer to below codes sample:

                ApplicationDbContext d1 = new ApplicationDbContext();
                d1.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
                var result =
             (from user in d1.Users
             orderby user.Id
             select new
             {
                 UserId = user.Id,
                 Username = user.UserName
             }).ToList();
                var count = result.Count;

    Result:

    best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 5, 2018 7:20 AM

All replies

  • User1120430333 posted

    You can use a Linq projection. The order by would be done as normal.

    var results = (from a in db.TableA where a.ID > 2 and a.ID < 100 orderby a.ID select new //  look at  Linq projection tutorial

    You project using a custom type/class/object which are read/write objects after they have been created.

    You can use an Anonymous types, which are read only objects after they have been created. 

    https://csharp-station.com/Tutorial/Linq/Lesson02

    The data in either case is going to be returned as a results, a collection. Notice the naming convention of 'results' a plural -- more than one. 

    int count = results.count();

    var ordered = results.Orderby(a => a.ID); // you can do that too after you get the results. 

    Sunday, June 3, 2018 3:03 AM
  • User283571144 posted

    Hi slimbunny,

    Is it possible to make one call to the database and
    get fields with row count?

    As far as I know, we couldn't directly get the selected count from Linq with orderby.

    I suggest you could firstly get the list of model by using the Linq.

    Then we could use List.Count method to get the result's count.

    It will just make one call to database.

    More details, you could refer to below codes sample:

                ApplicationDbContext d1 = new ApplicationDbContext();
                d1.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
                var result =
             (from user in d1.Users
             orderby user.Id
             select new
             {
                 UserId = user.Id,
                 Username = user.UserName
             }).ToList();
                var count = result.Count;

    Result:

    best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 5, 2018 7:20 AM