none
LINQ to ADO.NET-LINQ to DataSet (pubs) in Console application: Why " where author.Field<string>("State") == "CA" " fails? RRS feed

  • Question

  • Hi all,

    In my Visual C# 2008 Express, I ran the following code:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.ComponentModel;
    using System.Data;
    
    namespace LINQ_ConsoleLee439
    {
        class Program
        {
            static void Main(string[] args)
            {
    
                SqlConnection conn;
                SqlCommand comm;
                SqlDataAdapter adapter;
                DataSet ds = new DataSet();
    
                //---loads the Authors table into dataset---
                conn = new SqlConnection(@"Data Source=NAB-WK-02554356\SQLSCOTT;" +
                      "Initial Catalog=pubs;Integrated Security=True");
                comm = new SqlCommand("SELECT * FROM Authors", conn);
                adapter = new SqlDataAdapter(comm);
                adapter.Fill(ds);
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    Console.WriteLine("{0} <=> {1} {2}",
                       row["au_id"], row["au_fname"], row["au_lname"]);
                }
                //---Using LINQ to DataSet---
                //---query for authors living in CA---
                var authors =
                    (from author in ds.Tables[0].AsEnumerable()
                     where author.Field<string>("State") == "CA"
                     select new
                     {
                         ID = author.Field<string>("au_id"),
                         FirstName = author.Field<string>("au_fname"),
                         LastName = author.Field<string>("au_lname")
                     }).ToList();
    
                Console.ReadLine();
            }
        }
    }
    

    It gave me all the 23 Authors living in CA, IN, MD, TN, OR, KS, UT, MI that is not the code statement

    where

     

    author.Field<string>("State") == "CA"

     

     

    orders!!??


    Please help and tell me what is wrong in the code of my LINQ query.

    Thanks,
    Scott Chang 

     

    Tuesday, November 17, 2009 7:54 PM

Answers

  • I don't have a pubs database, but if using Northwind:

                    var empsInLondon = (from e in ds.Tables[0].AsEnumerable()
                               where e.Field<string>("City") == "London"
                               select new
                               {
                                   LastName = e.Field<string>("LastName"),
                                   FirstName = e.Field<string>("FirstName"),
                                   City = e.Field<string>("City")
                               }).ToList();
    
                    foreach (var employee in empsInLondon)
                    {
                        Console.WriteLine("City: {0}, Name: {1}, {2}", employee.City, employee.LastName, employee.FirstName);
                    }

    //Michael
    This posting is provided "AS IS" with no warranties.
    • Marked as answer by Scott_Chang Thursday, November 19, 2009 1:21 PM
    Thursday, November 19, 2009 8:14 AM

All replies

  • How do you know that all authors are returned in the LINQ query?

    In the code above you are writing out all the results of SELECT * FROM Authors, not the result from the LINQ query.
    The LINQ query itself should work fine.

    //Michael
    This posting is provided "AS IS" with no warranties.
    Wednesday, November 18, 2009 8:49 AM
  • Hi Michael,  Thanks for your response.

    I see your point.  How can I get that all authors are return in the LINQ query? How can I write out the right result (i. e. only Authors linving in CA) from the LINQ query? I do not know how to program this project.  Please help and advise again. 

    Thanks,
    Scott Chang
    Wednesday, November 18, 2009 12:45 PM
  • I don't have a pubs database, but if using Northwind:

                    var empsInLondon = (from e in ds.Tables[0].AsEnumerable()
                               where e.Field<string>("City") == "London"
                               select new
                               {
                                   LastName = e.Field<string>("LastName"),
                                   FirstName = e.Field<string>("FirstName"),
                                   City = e.Field<string>("City")
                               }).ToList();
    
                    foreach (var employee in empsInLondon)
                    {
                        Console.WriteLine("City: {0}, Name: {1}, {2}", employee.City, employee.LastName, employee.FirstName);
                    }

    //Michael
    This posting is provided "AS IS" with no warranties.
    • Marked as answer by Scott_Chang Thursday, November 19, 2009 1:21 PM
    Thursday, November 19, 2009 8:14 AM
  • Hi Michael,  Thank you very much for your nice response and coding.  It enlightened me greatly in doing LINQ query.  Thanks again,  Scott Chang
    Thursday, November 19, 2009 1:20 PM