Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

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

0
bmanmike39
Asked:
bmanmike39
3 Solutions
 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now