Simple LINQ to SQL Statement

Question

Hello, How can i achieve the following;

using the northwind database, i am trying to retrieve all the orders which contain a particular Product ID.

Note that the OrderDetails Table is between the orders and products table.

What LINQ TO SQL statement will i use?

thanks

Answer 1

var q = from od in dc.OrderDetails where od.ProductID == 123 select od.Order;

...or...

var q = dc.OrderDetails.Where(od => od.ProductID == 123).Select(o => o.Order);

---

Kristofer - Huagati Systems Co., Ltd.
Cool tools for Linq-to-SQL and Entity Framework:
huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

Answer 2

thanks.

i am trying to use this with predicatebuilder but i have a bit of a challenge using your code.

My code snippet is below;

I am trying to use Your code example in a similar scenario below;

i have 3 tables..Applicants, Application and Vacancies.

Each Applicant has zero, one or more entries into Applications.

Each Application entry has a VacancyID in each record.

My predicatebuilder code is below;

 var predicate = PredicateBuilder.True<Applicants>();
            if (chkVacancy == true)
            {
                predicate = predicate.Or(e => e.VacancyID == intVacancy);

                //var q = dc.Applications.Where(od => od.VacancyID == 123).Select(o => o.Applicants);

            }

i want to return all applicants who applied for a a particular vacancy (intVacancy). I really dont think this is difficult but i cant seem to come up with the LINQ for it.

how will i write the predicatebuilder in my case? As you can see, i am trying to return Applicants.

Thanks

Answer 3

if u want only one record

do as follows

using(YourDataContext context = new YourDataContext)

{

    var Result = (from temp in context.OrderDetails where context.ProductID = 12 select temp).FirstOrDefault();

    Result.xxx   ------------(xxx are your columns)

}

 

if u want multiple records(if exixts) with same productid

do as follows

using(YourDataContext context = new YourDataContext)

{

    var Result = (from temp in context.OrderDetails where context.ProductID = 12 select temp).ToList();

    foreach (item in Result)

   {  

       item.xxx   ------------(xxx are your columns)

   }

}

 

 

Answer 4

if u want only one record

do as follows

using(YourDataContext context = new YourDataContext)

{

    var Result = (from temp in context.OrderDetails where context.ProductID = 12 select temp).FirstOrDefault();

    Result.xxx   ------------(xxx are your columns)

}

 

if u want multiple records(if exixts) with same productid

do as follows

using(YourDataContext context = new YourDataContext)

{

    var Result = (from temp in context.OrderDetails where context.ProductID = 12 select temp).ToList();

    foreach (item in Result)

   {  

       item.xxx   ------------(xxx are your columns)

   }

}

You don't need the .ToList() in the second query; it's an unnecessary overhead.

I'm not a great fan of "var". Although it is necessary in some cases (such as new anonymous types), - I think it's makes for lazy programming and is not so readable for less familiar developers. In the first query, Result is of type OrderDetail. In the second it is IQueryable<OrderDetail> without the .ToList() and List<OrderDetail> when you have the .ToList().

After the first query and because of the FirstOrDefault(), then the code should check for no record being returned otherwise Result.xxx would cause a null exception. Either "if (Result != default (OrderDetail))" or "if (Result != null)" - they are effectively the same in this case.

Answer 5

I am trying to use Your code example in a similar scenario below;

i have 3 tables..Applicants, Application and Vacancies.

Each Applicant has zero, one or more entries into Applications.

Each Application entry has a VacancyID in each record.

My predicatebuilder code is below;

 var predicate = PredicateBuilder.True<Applicants>();
            if (chkVacancy == true)
            {
                predicate = predicate.Or(e => e.VacancyID == intVacancy);

                //var q = dc.Applications.Where(od => od.VacancyID == 123).Select(o => o.Applicants);

            }

i want to return all applicants who applied for a a particular vacancy (intVacancy). I really dont think this is difficult but i cant seem to come up with the LINQ for it.

how will i write the predicatebuilder in my case? As you can see, i am trying to return Applicants.

Thanks

I don't use predicate builder, but you could do it with something like this.

IQueryable<Applicant> q0 = from xxx in context.Applications
  join person in context.Applicants on person.ApplicantId equals xxx.ApplicantId
  where xxx.VacancyID == 123
  select person;

// The first query might return the same applicant if they have submitted multiple applications for the same Vacancy. If so then use Distinct

IQueryable<Applicant> q1 = q0.Distinct();

John