Solved

how to query datatable using linq?

Posted on 2011-03-12
6
754 Views
Last Modified: 2013-12-17
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

0
Comment
Question by:njgroup
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Gary Davis
ID: 35115920
Seems like you want to have the DataTable Rows collection as enumerable, not the DataTable itself.

Gary Davis
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 35116191
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
 

Author Comment

by:njgroup
ID: 35119903
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:njgroup
ID: 35119934
Ok, I solve the problem :D thanks very much
0
 

Author Closing Comment

by:njgroup
ID: 35119936
solution is perfect
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 35121463
Not a problem, glad I was able to help.  ;=)
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question