0

I am new in C# and have a quick question about the LINQ. I have a database which has two tables Re and De. The Re table has "Time" and "Id" columns. De table has "Id" and "data" columns. Now I want to get 5 entries from Re everyday and these 5 entries have to be randomly selected by time within that day. How can I do that. In addition, is there any kind of random number seed such that I can reproduce the randomness ? I tried couple of times, but it doesn't work.

Here is my code:

using (var db = new DBContext("Name=MyDB"))
{
    var Query = db.Re.Join(db.De,
                           r => r.Id,
                           d => d.Id,
                           (r, d) => new { r, d })
                     .Where(cr => cr.r.Time >= new DateTime(2016, 1, 1)
                                  && cr.r.Time < new DateTime(2016, 1, 30))
                     .OrderByDescending(cr => cr.r.Id)             
                     .GroupBy(a => new { y = a.r.Time.Year,
                                         m = a.r.Time.Month,
                                         d = a.r.Time.Day })
                     .SelectMany(g=>g.OrderBy(row => Guid.newGuid()).Take(200))
                     .Select(e => new
                                  {
                                      myId = e.r.Id,
                                      mydata = e.d.data
                                  })
                     .Take(100);
}
Mrk421
  • 5
  • 3
  • 3
    Can you explain "it doesn't work"? The `Guid.NewGuid()` trick is the standard quickest solution to selecting random records, which you are using. Perhaps the error is not in the randomization? Further details (an exception message, your results (current vs. expected), sample data, etc. Anything can help us here but a convoluted query isn't very helpful on its own. – Cᴏʀʏ May 05 '16 at 00:58
  • @CORY, it doesn't work in the sense of skipping couple of days. It took the results from January 1st but then skips January 2nd for unknown reason. Also in order to reproduce the randomness, I want to know if there is a way to assign the random number seed or not. Thanks. – Mrk421 May 05 '16 at 01:05
  • For the second part of your question, no, there is no way to seed the Guid generation. – Cᴏʀʏ May 05 '16 at 01:07
  • You mention that you're selecting from `Re`, but where does `De` figure in? – Scott Hannen May 05 '16 at 01:11
  • 2
    `Guid.newGuid()` is not guaranteed to be random - it's only ever guaranteed to be unique. You should use a true RNG. – Enigmativity May 05 '16 at 01:12
  • @CORY, thanks. Then is there another way in LINQ where you can have randomness also assign a random seed ? – Mrk421 May 05 '16 at 01:14
  • @ScottHannen, Sorry, I also select De, I just didn't write them. – Mrk421 May 05 '16 at 01:16
  • See also http://stackoverflow.com/a/3345272/224370 for another technique for selecting rows at random from SQL – Ian Mercer May 05 '16 at 01:39
  • If you want to pick records at random from _within a day_ then _add a filter to your LINQ_ to select only within the day. Or are you saying you want say 30 days with 5 random rows from each day = 150 records? Note: You **really** should take the time to explain this properly at the very start. – Nick.Mc May 05 '16 at 01:44
  • how about this solution http://stackoverflow.com/questions/4474053/select-n-random-records-with-linq – user786 May 05 '16 at 05:44
  • @IanMercer It help, thanks. – Mrk421 May 05 '16 at 16:20
  • @Nick.McDermaid no, I don't want filter, I want 5 random records from each day. – Mrk421 May 05 '16 at 16:20
  • In SQL you can use `OVER` to partition records as you require. I don't know if LINQ supports `OVER`. You might just have to loop around each day. I suggest you supply an example in your question because it's still quite unclear what you're really after – Nick.Mc May 05 '16 at 23:01

0 Answers0