how to query datatable using linq?

hi,

I have datatable, and I wanna query specific customer record from datatable using his id by linq,

so how can I do that using linq?

while I was trying, I got null value selected as return !, here is my code snippet:

 
private Customer GetCustomerInfo(string id)
        {
            Customer cm = null;
            var customers = (from customer in dtCustomers.AsEnumerable()
                         //where customer.Field<string>("id") == id
                         select new { 
                             CustomerId = customer["id"],
                             CustomerEnName = customer["EnName"],
                             CustomerArName = customer["ArName"],
                             CustomerClass = customer["EnClass"],
                             CustomerCellPhone = customer["CellPhone"],
                             Telephone = customer["Telephone"],
                             CustomerEmail = customer["Email"],
                             CustomerIsActive = customer["Active"],
                             CustomerNationalId = customer["NationalId"],
                             CustomerNationalIdType = customer["NationalIdType"]
                         }).Take(1);

            foreach (var c in customers)//I got customers always null even the id is correct
            {
                cm = new Customer(c.CustomerId.ToString(), "LMR-" + Other.changeTo6Digits(c.CustomerId.ToString()), c.CustomerEnName.ToString(), c.CustomerArName.ToString(), "", c.CustomerCellPhone.ToString(), c.Telephone.ToString(), c.CustomerEmail.ToString(), c.CustomerClass.ToString(), c.CustomerIsActive.ToString(), "", c.CustomerNationalId.ToString(), c.CustomerNationalIdType.ToString());
                break;
            }
            return cm;

        }

Open in new window

private Customer GetCustomerInfo(string id)
        {
            Customer cm = null;
            var customers = (from customer in dtCustomers.AsEnumerable()
                         //where customer.Field<string>("id") == id
                         select new { 
                             CustomerId = customer["id"],
                             CustomerEnName = customer["EnName"],
                             CustomerArName = customer["ArName"],
                             CustomerClass = customer["EnClass"],
                             CustomerCellPhone = customer["CellPhone"],
                             Telephone = customer["Telephone"],
                             CustomerEmail = customer["Email"],
                             CustomerIsActive = customer["Active"],
                             CustomerNationalId = customer["NationalId"],
                             CustomerNationalIdType = customer["NationalIdType"]
                         }).Take(1);

            foreach (var c in customers)//I got customers always null even the id is correct
            {
                cm = new Customer(c.CustomerId.ToString(), "LMR-" + Other.changeTo6Digits(c.CustomerId.ToString()), c.CustomerEnName.ToString(), c.CustomerArName.ToString(), "", c.CustomerCellPhone.ToString(), c.Telephone.ToString(), c.CustomerEmail.ToString(), c.CustomerClass.ToString(), c.CustomerIsActive.ToString(), "", c.CustomerNationalId.ToString(), c.CustomerNationalIdType.ToString());
                break;
            }
            return cm;

        }

Open in new window

njgroupAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi njgroup;

A couple of things your query returns a Anonymous type and NOT a Customer. We know this because in your select clause you have this, select new {, which does not name the type to create and so an Anonymous type is created. Note in the code snippet I have changed that to this, new DtCustomer {, which states to create the new objects of type DtCustomer, the class I also added to the code snippet. An Anonymous type really has no use outside of the method that created it therfore the reason for creating a concrete type DtCustomer to return to the caller. I also changed the Take(1) to FirstOrDefault(). The Take() method returns, "If count is less than or equal to zero, source is not enumerated and an empty IEnumerable(Of T) is returned.", where the FirstOrDefault returns a null if no result was found, better for error checking.

private DtCustomer GetCustomerInfo(string id)
{
    var customer  = (from customer in dtCustomers.AsEnumerable()
                     where customer.Field<string>("id") == id
                     select new DtCustomer { 
                         CustomerId = customer.Field<int>("id"),
                         CustomerEnName = customer.Field<string>("EnName"),
                         CustomerArName = customer.Field<string>("ArName"),
                         CustomerClass = customer.Field<string>("EnClass"),
                         CustomerCellPhone = customer.Field<string>("CellPhone"),
                         Telephone = customer.Field<string>("Telephone"),
                         CustomerEmail = customer.Field<string>("Email"),
                         CustomerIsActive = customer.Field<string>("Active"),
                         CustomerNationalId = customer.Field<int>("NationalId"),
                         CustomerNationalIdType = customer.Field<string>("NationalIdType")
                     }).FirstOrDefault();

    return customer;
}

public class DtCustomer
{
    public int CustomerId {get; set;}    
    public string CustomerEnName {get; set;}    
    public string CustomerArName {get; set;}    
    public string CustomerClass {get; set;}    
    public string CustomerCellPhone {get; set;}    
    public string Telephone {get; set;}
    public string CustomerEmail {get; set;}
    public string CustomerIsActive {get; set;}
    public int CustomerNationalId {get; set;}
    public string CustomerNationalIdType {get; set;} 
}

Open in new window


Fernando
0
 
Gary DavisDir Internet SvcsCommented:
Seems like you want to have the DataTable Rows collection as enumerable, not the DataTable itself.

Gary Davis
0
 
njgroupAuthor Commented:
thanks very much, but I get an strange error on where statement

Unable to cast object of type 'System.Int32' to type 'System.String'.


even I have it as sting but I dont know really why it give variable as integer!



 
private Customer GetCustomerInfo(string id)
        {
            var customer1 = (from customer in dtCustomers.AsEnumerable()
                            where customer.Field<string>("id") == id // <-- exception generated here
                            select new Customer
                            {
                                id = customer.Field<string>("id"),
                                enName = customer.Field<string>("EnName"),
                                arName = customer.Field<string>("ArName"),
                                type = customer.Field<string>("EnClass"),
                                cellPhone = customer.Field<string>("CellPhone"),
                                tel = customer.Field<string>("Telephone"),
                                email = customer.Field<string>("Email"),
                                isActive = customer.Field<string>("Active"),
                                nationalId = customer.Field<string>("NationalId"),
                                nationalIdType = customer.Field<string>("NationalIdType")
                            }).FirstOrDefault();

            return customer1;
        }

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
njgroupAuthor Commented:
Ok, I solve the problem :D thanks very much
0
 
njgroupAuthor Commented:
solution is perfect
0
 
Fernando SotoRetiredCommented:
Not a problem, glad I was able to help.  ;=)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.