-1

What is the best (and fastest) way to retreive random rows using Linq to SQL with unique data / no duplicate record? oh i preffer to do it in 1 statement, does it possible? i found this relevant question but i don't think that this approach resulting unique records.

i have tried this so far :

//first approach

AirAsiaDataContext LinqDataCtx = new AirAsiaDataContext();
var tes = (from u in LinqDataCtx.users.AsEnumerable()
           orderby Guid.NewGuid()
           select u).Take(5);  

//second approach

var usr = from u in LinqDataCtx.users
          select u;

 int count = usr.Count(); // 1st round-trip
 int index = new Random().Next(count);

 List<user> tes2 = new List<user>();
 for (int i = 0; i < 5; i++)
 {
      tes2.Add(usr.Skip(index).FirstOrDefault()); // 2nd round-trip    
 }

as you can see above, i have tried 2 solution, it works, but above codes did not resulting unique records, there are chances for duplicate.

Community
  • 1
  • 1
NomNomNom
  • 811
  • 3
  • 12
  • 37
  • you can use `.Distinct()` in your linq query to get unique records. And then perform the `Random`. – Apostrofix Aug 18 '14 at 08:56
  • u miss understand something "var usr = from u in LinqDataCtx.users select u;" this part give me unique records, the problem is when i am adding to tes2, it might give me duplicate records, coz i am still using usr as source. – NomNomNom Aug 18 '14 at 09:07
  • then before adding it, check to see if the record already exists in tes2. – Apostrofix Aug 18 '14 at 09:35
  • yeah, i've thought about that too, but that's not what i want, what i want is to randomly get unique records throught linq, not checking it manually. – NomNomNom Aug 18 '14 at 10:05
  • The first part of the answer to the question you link to (ie the Fake UDF) will sort all the records in a random order and then select the first 5. So they only way this could return duplicates if is you have duplicates in your table to start with. – sgmoore Aug 18 '14 at 18:00

1 Answers1

-1
db.TableName.OrderBy(x=>Guid.NewGuid()).FirstOrDefault();

If you want to take unique data / no duplicate record, you'd better to use another list to store the row which you taked already.

Tim.Tang
  • 3,158
  • 1
  • 15
  • 18
  • thx tim, but i want to get all record with no duplicate in 1 statement, so i don't need to do loop, probrably i will remove .FirstOrDefault(). – NomNomNom Aug 18 '14 at 06:29