CODEWITHSUNDEEP
linq-to-sql
Herb Caudill
Herb Caudill

Reputation: 50002

What's the Linq to SQL equivalent to TOP or LIMIT/OFFSET?

How do I do this

Select top 10 Foo from MyTable

in Linq to SQL?

Upvotes: 202

Views: 189631

Answers (13)

Adam Lassek
Adam Lassek

Reputation: 35515

Use the Take method:

var foo = (from t in MyTable
           select t.Foo).Take(10);

In VB LINQ has a take expression:

Dim foo = From t in MyTable _
          Take 10 _
          Select t.Foo

From the documentation:

Take<TSource> enumerates source and yields elements until count elements have been yielded or source contains no more elements. If count exceeds the number of elements in source, all elements of source are returned.

Upvotes: 256

Gladson Reis
Gladson Reis

Reputation: 13

This way it worked for me:

var noticias = from n in db.Noticias.Take(6)
                       where n.Atv == 1
                       orderby n.DatHorLan descending
                       select n;

Upvotes: 0

Inc33
Inc33

Reputation: 1921

The OP actually mentioned offset as well, so for ex. if you'd like to get the items from 30 to 60, you would do: 

var foo = (From t In MyTable
       Select t.Foo).Skip(30).Take(30);

Use the "Skip" method for offset.
Use the "Take" method for limit.

Upvotes: 35

David Alpert
David Alpert

Reputation: 3187

In VB:

from m in MyTable
take 10
select m.Foo

This assumes that MyTable implements IQueryable. You may have to access that through a DataContext or some other provider.

It also assumes that Foo is a column in MyTable that gets mapped to a property name.

See http://blogs.msdn.com/vbteam/archive/2008/01/08/converting-sql-to-linq-part-7-union-top-subqueries-bill-horst.aspx for more detail.

Upvotes: 147

C0L.PAN1C
C0L.PAN1C

Reputation: 12243

I had to use Take(n) method, then transform to list, Worked like a charm:

    var listTest = (from x in table1
                     join y in table2
                     on x.field1 equals y.field1
                     orderby x.id descending
                     select new tempList()
                     {
                         field1 = y.field1,
                         active = x.active
                     }).Take(10).ToList();

Upvotes: 0

minhnguyen
minhnguyen

Reputation: 91

Array oList = ((from m in dc.Reviews
                           join n in dc.Users on m.authorID equals n.userID
                           orderby m.createdDate descending
                           where m.foodID == _id                      
                           select new
                           {
                               authorID = m.authorID,
                               createdDate = m.createdDate,
                               review = m.review1,
                               author = n.username,
                               profileImgUrl = n.profileImgUrl
                           }).Take(2)).ToArray();

Upvotes: 2

amcoder
amcoder

Reputation: 1151

Use the Take(int n) method:

var q = query.Take(10);

Upvotes: 35

user124368
user124368

Reputation: 61

Whether the take happens on the client or in the db depends on where you apply the take operator. If you apply it before you enumerate the query (i.e. before you use it in a foreach or convert it to a collection) the take will result in the "top n" SQL operator being sent to the db. You can see this if you run SQL profiler. If you apply the take after enumerating the query it will happen on the client, as LINQ will have had to retrieve the data from the database for you to enumerate through it

Upvotes: 5

Yann
Yann

Reputation: 141

@Janei: my first comment here is about your sample ;)

I think if you do like this, you want to take 4, then applying the sort on these 4.

var dados =  from d in dc.tbl_News.Take(4) 
                orderby d.idNews descending
                select new 
                {
                    d.idNews,
                    d.titleNews,
                    d.textNews,
                    d.dateNews,
                    d.imgNewsThumb
                };

Different than sorting whole tbl_News by idNews descending and then taking 4

var dados =  (from d in dc.tbl_News
                orderby d.idNews descending
                select new 
                {
                    d.idNews,
                    d.titleNews,
                    d.textNews,
                    d.dateNews,
                    d.imgNewsThumb
                }).Take(4);

no ? results may be different.

Upvotes: 14

Anton
Anton

Reputation: 39

Taking data of DataBase without sorting is the same as random take

Upvotes: 2

Janei Vieira
Janei Vieira

Reputation: 49

I do like this:

 var dados =  from d in dc.tbl_News.Take(4) 
                orderby d.idNews descending

                select new 
                {
                    d.idNews,
                    d.titleNews,
                    d.textNews,
                    d.dateNews,
                    d.imgNewsThumb
                };

Upvotes: 4

spdrcr911
spdrcr911

Reputation: 51

This works well in C#

var q = from m in MyTable.Take(10)
        select m.Foo

Upvotes: 5

FlySwat
FlySwat

Reputation: 175733

You would use the Take(N) method.

Upvotes: 3

Related Questions