none
Add Where Statement TO LINQ Query RRS feed

  • Question

  • I have the below LINQ query which will return all results, I was wondering if I could modify this somehow to add a WHERE clause to subset the returned result set.

    This is what I have

    using (XamarinEntities entities = new XamarinEntities())
    	return entities.employeeDatas.FirstOrDefault(e => e.ID == iD);

    I was wondering if I could somehow say WHERE e.ID = "2" and ONLY return the result set of id # 2?

    Sunday, November 18, 2018 9:12 PM

Answers

  • Hello,

    The following is using Entity Framework 6 against SQL-Server NorthWind database.

    In this case I query Order table, include several child tables (nav properties), Customer, OrderDetails, Product.

    public Order GetOrder(int pIdentifier)
    {
        using (var context = new NorthWindEntities())
        {
            return context.Orders
                .Include(x => x.Customer)
                .Include(x => x.OrderDetails.Select(y => y.Product))
                .Where(x => x.OrderID == pIdentifier)
                .FirstOrDefault();
        }
    }

    Results when calling the following code.

    The order details comes after/on the order query.

    int orderIdentier = 10248;
    var order = GetOrder(orderIdentier);
    if (order != null)
    {
                    
        var orderDetail = order.OrderDetails.Where(prod => prod.Quantity >= 10).FirstOrDefault();
        MessageBox.Show($"Ship date is {orderDetail.Order.ShippedDate.Value.ToShortDateString()}\nfor order id {orderIdentier}\nwas shipped to {orderDetail.Order.ShipCity}\nCustomer '{orderDetail.Order.Customer.CompanyName}'");
    
    }
    else
    {
        MessageBox.Show($"Failed to find order with id of {orderIdentier}");
    }

    Do a tad bit more assertion

    int orderIdentier = 10248;
    var order = GetOrder(orderIdentier);
    if (order != null)
    {
        var orderDetail = order.OrderDetails.FirstOrDefault(prod => prod.Quantity >= 10);
        if (orderDetail != null) MessageBox.Show($"Ship date is {orderDetail.Order.ShippedDate.Value.ToShortDateString()}\nfor order id {orderIdentier}\nwas shipped to {orderDetail.Order.ShipCity}\nCustomer '{orderDetail.Order.Customer.CompanyName}'");
    }
    else
    {
        MessageBox.Show($"Failed to find order with id of {orderIdentier}");
    }
    
    Does this get the lightbulb going?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by IndigoMontoya Monday, November 19, 2018 2:50 AM
    Monday, November 19, 2018 2:41 AM
    Moderator

All replies

  • You want to find an item where id = 2 and then you want to return only the id?
    Sunday, November 18, 2018 10:10 PM
  • You want to find an item where id = 2 and then you want to return only the id?

    That's just an example.  I want to return the id that will be passed from the user but only data for that ID.

    Sunday, November 18, 2018 10:18 PM
  • Sorry but I don't understand. What was wrong with FirstOrDefault? Or do you want to use Where to get more than one result?
    • Edited by DerChris88 Sunday, November 18, 2018 10:25 PM
    Sunday, November 18, 2018 10:24 PM
  • I want to use a where to get a specific result.  FirstOrDefault would always return the data for ID 1, correct?

    What if my user only wanted to see the data for ID 4?  Or ID 10?

    Sunday, November 18, 2018 11:24 PM
  • No, FirstOrDefault returns the data for the requested id.

    using (XamarinEntities entities = new XamarinEntities())
    	return entities.employeeDatas.FirstOrDefault(e => e.ID == iD);

    If iD = 1 it returns the data for ID 1, if iD = 4 it returns data for ID 4...

    Sunday, November 18, 2018 11:29 PM
  • No, FirstOrDefault returns the data for the requested id.

    using (XamarinEntities entities = new XamarinEntities())
    	return entities.employeeDatas.FirstOrDefault(e => e.ID == iD);

    If iD = 1 it returns the data for ID 1, if iD = 4 it returns data for ID 4...

    Wait - so I have misunderstood this and the .... is the ID that is being passed?  Is that accurate?

    == iD)
    Sunday, November 18, 2018 11:44 PM
  • Yes, iD is the value for the comparison. You will get the first found object where ID == iD or if none is found you get null(default). But if there are more than one you will just get the first found object from the list.

    • Edited by DerChris88 Monday, November 19, 2018 12:26 AM
    Monday, November 19, 2018 12:24 AM
  • var entity = new employeeDatas(); using (XamarinEntities entities = new XamarinEntities()) { entity = (entities.employeeDatas.Where(e => e.ID == id).SingleOrDefault();

    }

    return entity;



    Monday, November 19, 2018 2:35 AM
  • Hello,

    The following is using Entity Framework 6 against SQL-Server NorthWind database.

    In this case I query Order table, include several child tables (nav properties), Customer, OrderDetails, Product.

    public Order GetOrder(int pIdentifier)
    {
        using (var context = new NorthWindEntities())
        {
            return context.Orders
                .Include(x => x.Customer)
                .Include(x => x.OrderDetails.Select(y => y.Product))
                .Where(x => x.OrderID == pIdentifier)
                .FirstOrDefault();
        }
    }

    Results when calling the following code.

    The order details comes after/on the order query.

    int orderIdentier = 10248;
    var order = GetOrder(orderIdentier);
    if (order != null)
    {
                    
        var orderDetail = order.OrderDetails.Where(prod => prod.Quantity >= 10).FirstOrDefault();
        MessageBox.Show($"Ship date is {orderDetail.Order.ShippedDate.Value.ToShortDateString()}\nfor order id {orderIdentier}\nwas shipped to {orderDetail.Order.ShipCity}\nCustomer '{orderDetail.Order.Customer.CompanyName}'");
    
    }
    else
    {
        MessageBox.Show($"Failed to find order with id of {orderIdentier}");
    }

    Do a tad bit more assertion

    int orderIdentier = 10248;
    var order = GetOrder(orderIdentier);
    if (order != null)
    {
        var orderDetail = order.OrderDetails.FirstOrDefault(prod => prod.Quantity >= 10);
        if (orderDetail != null) MessageBox.Show($"Ship date is {orderDetail.Order.ShippedDate.Value.ToShortDateString()}\nfor order id {orderIdentier}\nwas shipped to {orderDetail.Order.ShipCity}\nCustomer '{orderDetail.Order.Customer.CompanyName}'");
    }
    else
    {
        MessageBox.Show($"Failed to find order with id of {orderIdentier}");
    }
    
    Does this get the lightbulb going?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by IndigoMontoya Monday, November 19, 2018 2:50 AM
    Monday, November 19, 2018 2:41 AM
    Moderator