Solved

How would I write the following query in Linq?

Posted on 2014-11-13
5
297 Views
Last Modified: 2016-02-15
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
Comment
Question by:bmanmike39
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 11

Expert Comment

by:LordWabbit
ID: 40441969
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
 
LVL 11

Assisted Solution

by:LordWabbit
LordWabbit earned 167 total points
ID: 40441976
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 40441978
What's wrong with what you have? Really, only the ToList could be taken out (or maybe moved to the end).
0
 
LVL 18

Accepted Solution

by:
Richard Lee earned 167 total points
ID: 40442066
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
 
LVL 34

Assisted Solution

by:it_saige
it_saige earned 166 total points
ID: 40443592
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question