Cannot implicitly convert type 'System.Linq.IQueryable<int>' to 'int?'

Question

var cityList = from country in 
                    doc.Element("result")
                    .Element("cities")
                    .Descendants("city")
select new {
        Name = country.Element("name").Value,
        Code = country.Element("code").Value,
        CountryCode = int.Parse(country
                      .Element("countrycode")
                      .Value)
    };

foreach(var citee in cityList)
{
    City city = new City();
    city.CountryID = from cnt in db.Countries 
                     where cnt.DOTWInternalID == citee.CountryCode 
                     select cnt.ID;
}

I'm getting an error on the second query as seen in the title of this post. I tried converting to int to nullable int but nothing worked. Help me, guys.

Thanks

Answer 1

it will return an iQueryable, you will need to do something like using the First

cit.CountryID = db.Countries.First(a=>a.DOTWInternalID == citee.CountryCode).ID

Answer 2

It has elapsed a long time since the last update to the post but i think it's worth improving the solution.

In my opinion the solutions posted for this particular scenario are not the best way in terms of performace to get the ID you need. A better solution is as follows.

db.Countries.Where(a=>a.DOTWInternalID == citee.CountryCode)
            .Select(a => a.ID).FirstOrDefault();

The previous statemants basically runs a SQL query similar to the following one:

SELECT TOP (1) ID
FROM [dbo].[Countries]
WHERE DOTWInternalID = 123

The proposed solutions work but basically do a "SELECT *" to create the entity with all the values and then obtain the ID from the object just created.

You can use Linqpad to actually see the generated SQL and tune up LINQ queries or Lambdas.

Hope it helps to some others that get to this post.

Answer 3

Here is the problem and solution

from cnt in db.Countries where cnt.DOTWInternalID == citee.CountryCode select cnt.ID part. If you omit the ID then it returns a Generic IEnumerable with Country(hoping that you have Country class). So what you have to do is first return the select criteria and select the first row then the ID field. Same like shown below.

cit.CountryID = (from cnt in db.Countries where cnt.DOTWInternalID == citee.CountryCode   select cnt).First<Country>().ID;

This will solve your problem.