Solved

how to query datatable using linq?

Posted on 2011-03-12
6
760 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 63

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
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 

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 63

Expert Comment

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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 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