none
C# Linq count

    Question

  • I would like help with a linq to sql statement in a C# application. I am trying to count the number of occurences in the
    Trans table. I am always getting a count of zero.  There is only one record in the IM and IPack tables but there are 1 to hundreds
    of records in the Trans table.

    var  varGoodTransCount = (from t in rData.Trans
                                             join iw in rData.Im on t.I_ID equals iw.I_ID
                                             join ip in rData.IPack on iw.PID equals ip.PID
                                             where iw.TNum == PkgID
                                             select new { }).Count();

    Thus can  you tell what what you think is wrong with the linq query above? Can you suggest how to change it?

    Thursday, October 04, 2012 7:58 PM

Answers

  • Right now, you're creating a new anonymous type.  To get the count, you'd typically just do:

    var  varGoodTransCount = (from t in rData.Trans
                                             join iw in rData.Im on t.I_ID equals iw.I_ID
                                             join ip in rData.IPack on iw.PID equals ip.PID
                                             where iw.TNum == PkgID 
                                             select t).Count();

    Bascially, you need to select something "real" - like the transaction, then call Count() on the results.  If you're using EF or LINQ to SQL, this will all get computed on the server.


    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Proposed as answer by Norkk Thursday, October 04, 2012 11:06 PM
    • Marked as answer by wendy elizabeth Friday, October 05, 2012 1:49 AM
    Thursday, October 04, 2012 9:24 PM
    Moderator

All replies

  • Use the DataTable.Compute method

    Thursday, October 04, 2012 8:02 PM
  • Can you show me code on how to accomplish this goal?
    Thursday, October 04, 2012 8:23 PM
  • Re-looking at your question, it may be more advantageous to use a DataRelation using a DataSet. This gives you a bit more flexibility.

    //Example

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                DataTable projects = new DataTable("Projects");
                projects.Columns.Add("ProjectRec", typeof(Int32));
                projects.Columns.Add("ProjectName", typeof(String));        
    
                DataTable files = new DataTable("Files");
                files.Columns.Add("FileRec", typeof(Int32));
                files.Columns.Add("FileName", typeof(String));
                files.Columns.Add("Title", typeof(String));
                files.Columns.Add("ProjectRec", typeof(Int32));
    
                projects.Rows.Add(1, "Math Project");
                projects.Rows.Add(2, "Science Project");
                files.Rows.Add(1, "Exponential Functions", "P.327", 1);
                files.Rows.Add(2, "Graphing Polynomial Functions", "P.225", 1);
                files.Rows.Add(3, "Analytic Geometry", "P. 290", 1);            
                files.Rows.Add(4, "Evolution", "Natural Selection", 2);
                files.Rows.Add(4, "Astronomy", "Our Solar System", 2);
    
                //Create DataSet with relation
                DataSet ds = new DataSet();
                ds.Tables.Add(projects);
                ds.Tables.Add(files);
                ds.Relations.Add("Projects_Files",
                    ds.Tables["Projects"].Columns["ProjectRec"],
                    ds.Tables["Files"].Columns["ProjectRec"],
                    false);
    
                foreach (DataRow row in ds.Tables["Projects"].Rows)
                {
                    Console.WriteLine("{0}, {1}", row["ProjectRec"], row["ProjectName"]);
                    foreach (DataRow childRow in row.GetChildRows("Projects_Files"))           
                        Console.WriteLine("\t" + childRow["FileName"]);
                }
                Console.ReadLine();
    
            }
        }
    }
    

    Thursday, October 04, 2012 8:35 PM
  • Or to get the count of its child rows like so:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                DataTable projects = new DataTable("Projects");
                projects.Columns.Add("ProjectRec", typeof(Int32));
                projects.Columns.Add("ProjectName", typeof(String));        
    
                DataTable files = new DataTable("Files");
                files.Columns.Add("FileRec", typeof(Int32));
                files.Columns.Add("FileName", typeof(String));
                files.Columns.Add("Title", typeof(String));
                files.Columns.Add("ProjectRec", typeof(Int32));
    
                projects.Rows.Add(1, "Math Project");
                projects.Rows.Add(2, "Science Project");
                files.Rows.Add(1, "Exponential Functions", "P.327", 1);
                files.Rows.Add(2, "Graphing Polynomial Functions", "P.225", 1);
                files.Rows.Add(3, "Analytic Geometry", "P. 290", 1);            
                files.Rows.Add(4, "Evolution", "Natural Selection", 2);
                files.Rows.Add(4, "Astronomy", "Our Solar System", 2);
    
                //Create DataSet with relation
                DataSet ds = new DataSet();
                ds.Tables.Add(projects);
                ds.Tables.Add(files);
                ds.Relations.Add("Projects_Files",
                    ds.Tables["Projects"].Columns["ProjectRec"],
                    ds.Tables["Files"].Columns["ProjectRec"],
                    false);
    
                foreach (DataRow row in ds.Tables["Projects"].Rows)
                {                
                    Int32 count = row.GetChildRows("Projects_Files").Count();
                    Console.WriteLine("{0} has {1} child rows", row["ProjectName"], count);
                }
                Console.ReadLine();
    
            }
        }
    }
    

    Thursday, October 04, 2012 8:38 PM
  • Right now, you're creating a new anonymous type.  To get the count, you'd typically just do:

    var  varGoodTransCount = (from t in rData.Trans
                                             join iw in rData.Im on t.I_ID equals iw.I_ID
                                             join ip in rData.IPack on iw.PID equals ip.PID
                                             where iw.TNum == PkgID 
                                             select t).Count();

    Bascially, you need to select something "real" - like the transaction, then call Count() on the results.  If you're using EF or LINQ to SQL, this will all get computed on the server.


    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Proposed as answer by Norkk Thursday, October 04, 2012 11:06 PM
    • Marked as answer by wendy elizabeth Friday, October 05, 2012 1:49 AM
    Thursday, October 04, 2012 9:24 PM
    Moderator