LINQ statement to retrieve random record with GUID

Hello all,

I am a newbie to LINQ

Recently I posted a question to get a random record.  I was using  a two step approach to first get the list then use a second list with .skip()  and .take() and a random number using the max records in the returned list.  I wanted to do this in one LINQ statement.  One of the members here Fernando Soto,  submitted an idea to use GUID as part of the mix which is random and doing a orderby on it.  I though the idea was brilliant, however it did not work.  I like the approach and think it is close. Any help please?  And again thanks to Fernando for suggesting the idea.

var AdList =
    (from a in Advertisements
     join c in Companies on a.AdvertiementOwnerID equals c.CompanyID
     where a.AdvertisementType=="Banner"
    orderby Guid.NewGuid()
     select new {
        a.AdvertisementID,
        a.AdvertisementType,
        a.AdvertiementActive,
        a.AdvertisementText,
        a.AdvertisementImageLocation,
        a.AdvertisementAlt,
        c.CompanyName,
        c.Website
    }).ToList();          // This will return all records in random order
   
//  }).FirstOrDefault();  // This will return one records randomly each time  
//  }).Take(X);           // This will return X random record


Here was my first question that had my original (bloated) approach

http://www.experts-exchange.com/questions/28690437/Simplify-LINQ-statement-getting-random-record.html
Charles BaldoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
What do you mean by "it did not work?" What behavior did you observe?
Fernando SotoRetiredCommented:
Hi charlesbaldo;

I stated in my last post in the previous question that the Linq query was, "a Linq to Entity Framework query ", I have tested it in both EF 6 and EF 5 and it does work. It will NOT work if you are using Linq to SQL. Entity Framework correctly translates the Guid.NewGuid() method to the T-SQL NEWID() method where Linq to SQL just drops the complete line orderby.

I have not tested in earlier versions of EF below version 5.

What is your configuration charlesbaldo?
Charles BaldoSoftware DeveloperAuthor Commented:
I am using SQL Server 2014, modeled with entity frameworks 6.0 and MVC 5
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Charles BaldoSoftware DeveloperAuthor Commented:
What I was seeing was the same record every time
Fernando SotoRetiredCommented:
Hi Chuck;

Did you place this line of the code, orderby Guid.NewGuid(), in the same place as shown in the question?
Charles BaldoSoftware DeveloperAuthor Commented:
Yes I did,  I copied it exactly from my code to here. I know it has to be close.
Fernando SotoRetiredCommented:
Chuck, I see you use LinqPad can you place the query into LinqPad and execute the query in it and when the query completes click on the SQL tab in the results window and see what Linq translated the Linq query into SQL query.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Charles BaldoSoftware DeveloperAuthor Commented:
Fernando,

Thank you

I am going to work this with LINQ pad.  I think its close and there is just something small I am overlooking.  If I still have issues I will re post a new issue with the reults

Thanks for all your help.  Again I though the idea was insightful and opened my eyes to the richness of LINQ
Fernando SotoRetiredCommented:
I did a test using a Linq query that had a join as well to make sure it was not the culprit causing the issue, it worked.

By the way when you executed the Linq query in LinqPad did the results come back in random order as it should have been?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.