How would I write the following query in Linq?

How would I write this query in Linq to sql

Invoice IVN = eo.Invoices.ToList().Where(x => x.InvoiceNumber == s).First();

Open in new window

bmanmike39Asked:
Who is Participating?
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.

LordWabbitCommented: 
Invoice invoice = db.Invoices.Where(s => s.InvoiceNumber == findInvoiceNumber).OrderBy(s => s.InvoiceNumber).First();

Open in new window


You would need to put some sort of order by in, otherwise you would not reliably get the record you are expecting depending on how the SQL execution plan changes (unless invoicenumber is unique).  The ToList() is not needed, since Invoices is already a collection.
0
LordWabbitCommented:
One more thing, First will throw a System.InvalidOperationException exception if the invoice does not exist, to avoid this you can use FirstOrDefault, if the invoice does not exist it will return null which you can then check for.  Throwing exceptions is expensive, and not a good way to check if a record exists.
0
käµfm³d 👽Commented:
What's wrong with what you have? Really, only the ToList could be taken out (or maybe moved to the end).
0
Richard LeeSoftware EnthusiastCommented:
When querying a database one of your aims is always performance since the DB can be a severe bottleneck in how your overall application performs.

ToList() forces the data to be retrieved immediately to the client before the next LINQ statement is executed. What you want is to take advantage of the IQueryable (constructing commands that execute on the server) and allow a single SQL command to be generated which will then only execute once on the DB server.

Also if InvoiceNumber is unique you will want to use Single() or SingleOrDefault() in your query instead of First(). This will optimize the SQL query and increase performance.

using(var eo = new YourDataContext())
{
   Invoice IVN = eo.Invoices
          .Where(x => x.InvoiceNumber == s)
          .SingleOrDefault();
}

Open in new window

0

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
it_saigeDeveloperCommented:
Are you asking how you would re-write what you have as an Expression-Based Query?

A Method-Based Query with Lambda Expressions - 
Invoice IVN = eo.Invoices.ToList().Where(x => x.InvoiceNumber == s).FirstOrDefault();

Open in new window


An Expression-Based Query - 
Invoice IVN = (from Invoice item in eo.Invoices where item.InvoiceNumber.Equals(s) select item).FirstOrDefault();

Open in new window


The thing is they are both LINQ statements.

-saige-
0
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
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.