How to setup this query using DbContext

Question

I have two tables in SQL server for customers and their addresses.

CREATE TABLE Customers
(
    ID              INT NOT NULL IDENTITY(1,1),
    LastName        VARCHAR(50) NOT NULL,
    FirstName       VARCHAR(50) NOT NULL,
    DateOfBirth     DATETIME2 NOT NULL,
    CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([ID] ASC)
)
CREATE TABLE CustomerAddresses
(
    ID              INT NOT NULL IDENTITY(1,1),
    CustomerID      INT NOT NULL CONSTRAINT [FK_CustomerAddresses_Customers] FOREIGN KEY([CustomerID]) REFERENCES [dbo].[Customers] ([ID]),
    Street          VARCHAR(100) NOT NULL,
    City            VARCHAR(50) NOT NULL,
    Country         VARCHAR(50) NOT NULL,
    CONSTRAINT [PK_CustomerAddresses] PRIMARY KEY CLUSTERED ([ID] ASC)
)

I have generated a EFdatamodel and connecting to it using DataContext. I am trying to get all customers for a particular country. My code is as follows.

static List<Customer> GetByCountry(string country)  
        {  
            MyDbContext MyContext = new MyDbContext ();  
            return MyContext.Customers.Where(x => x.CustomerAddresses.Where( y => y.Country == country)).ToList();  
        }

But I am getting following compilation errors.

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'bool' Cannot convert lambda expression to delegate type 'System.Func' because some of the return types in the block are not implicitly convertible to the delegate return type

What am I doing wrong here?

You are getting a compiler error. This means you don't need to concentrate on EF. Concentrate on a C# error you made. — usr
– usr, Commented Oct 4, 2012 at 21:48

SomebodyYouKnow · Accepted Answer · 2012-10-04 22:09:11Z

1

Your code must be

return MyContext.Customers.Where(x => x.CustomerAddresses.Any( y => y.Country == country)).ToList();

Because you want to return all Customers that have any address with the specified country. Where() expects a function that returns whether the condition is true, and returns an enumeration of all elements which hold this condition true. In your outer Where() your supply an argument that is of type IEnumerable (which is the return value of your inner where), and that is wrong.

answered Oct 4, 2012 at 22:09

SomebodyYouKnow

1811 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to setup this query using DbContext

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related