How do I setup Linq to SQL and WCF

Question

So I'm venturing out into the world of Linq and WCF web services and I can't seem to make the magic happen. I have a VERY basic WCF web service going and I can get my old SqlConnection calls to work and return a DataSet. But I can't/don't know how to get the Linq to SQL queries to work. I'm guessing it might be a permissions problem since I need to connect to the SQL Database with a specific set of credentials but I don't know how I can test if that is the issue. I've tried using both of these connection strings and neither seem to give me a different result.

<add name="GeoDataConnectionString" connectionString="Data Source=SQLSERVER;Initial Catalog=GeoData;Integrated Security=True"
         providerName="System.Data.SqlClient" />

<add name="GeoDataConnectionString" connectionString="Data Source=SQLSERVER;Initial Catalog=GeoData;User ID=domain\userName; Password=blahblah; Trusted_Connection=true"
         providerName="System.Data.SqlClient" />

Here is the function in my service that does the query and I have the interface add the [OperationContract]

public string GetCity(int cityId)
        {
            GeoDataContext db = new GeoDataContext();
            var city = from c in db.Cities
                       where c.CITY_ID == 30429
                       select c.DESCRIPTION;

            return city.ToString();
        }

The GeoData.dbml only has one simple table in it with a list of city id's and city names. I have also changed the "Serialization Mode" on the DataContext to "Unidirectional" which from what I've read needs to be done for WCF.

When I run the service I get this as the return: SELECT [t0].[DESCRIPTION] FROM [dbo].[Cities] AS [t0] WHERE [t0].[CITY_ID] = @p0

Dang, so as I'm writing this I realize that maybe my query is all messed up?

Answer 1

Try this:

public string GetCity(int cityId)
{
    GeoDataContext db = new GeoDataContext();
    var city = db.Cities.SingleOrDefault(c => c.CITY_ID == 30429);
    return city.DESCRIPTION;
}

The problem with your query is that it's not returning a string in the var, it's returning an IQueryable. So when you ToString() the IQueryable, it must be returning a string representation of the SQL query represented by the IQueryable.