locked
LINQ query to get column count (number of columns) RRS feed

  • Question

  • User-173651909 posted

    I'm need to execute a LINQ query in EF6 and I can't even figure it out in plain SQL. I have six fields in a table. The value is in usedpower and can be anything up from zero. I need to know how many of the six have values greater than 0.

    For example, if three fields contained 125 and the other three contained 0 then I want to return 3. Any ideas how to go about that? I throught of selecting the fields as a new object and get the count but it would return six even if there was nothing in there. Another option would be to perform six individual queries but there must be a cleaner way of doing that.

    Thanks in advance
    Adam

    Tuesday, January 26, 2016 3:34 PM

Answers

  • User-986267747 posted

    Hi Adam-M,

    I'm need to execute a LINQ query in EF6 and I can't even figure it out in plain SQL.

    I need to know how many of the six have values greater than 0.

    According to your description, you'd like to execute the query in Entitty Framework , I agree with mgebhard, his suggestion is great, but i suggest that you could use stored procedure to achieve the query, then we could call the stored procedure in Entitty Framework so that we could get the result you want to get.

    First, as mgebhard say, you need to create your table in your database.

    CREATE TABLE YourTable(
    	Id	INT,
    	Val1 INT,
    	Val2 INT,
    	Val3 INT,
    	Val4 INT,
    	Val5 INT,
    	Val6 INT,
    )
    
    INSERT INTO #YourTable(Id, Val1, Val2, Val3, Val4, Val5, Val6)
    VALUES	(1, 125,125,125,0,0,0),
    		(2, 0,125,125,0,0,0),
    		(3, 0,0,125,0,0,0);
    

    Second, you need to create your stored procedure as below.

         CREATE PROCEDURE [dbo].[GetCountThanZero]    
            AS
            BEGIN
    
    		WITH Value_Fields (Id, Val)
    		AS
    		(
    			SELECT Id, Val1 FROM [dbo].YourTable
    			UNION ALL
    			SELECT Id, Val2 FROM [dbo].YourTable
    			UNION ALL 
    			SELECT Id, Val3 FROM [dbo].YourTable
    			UNION ALL 
    			SELECT Id, Val4 FROM [dbo].YourTable
    			UNION ALL 
    			SELECT Id, Val5 FROM [dbo].YourTable
    			UNION ALL 
    			SELECT Id, Val6 FROM [dbo].YourTable
    		)
    
             
            SELECT Id, COUNT(Val) [Count]
    		FROM Value_Fields
    		WHERE Val > 0
    		GROUP BY Id 
            END
    
    
    

    Finally, you could get the result using stored procedure. Like this:

    using (var ctx = new SchoolDBEntities())
        {
            IList<Course> courseList = ctx.GetCoursesByStudentId(1).ToList<Course>();
            //do something with courselist here
        }
    
    public class Course
    {
       public int Id { get; set; }
    
       public int Count { get; set; }
    
    }

    Besides, you could refer to the following links to get more inforamtion and learn how to use stored procedure in ef.

    http://www.entityframeworktutorial.net/data-read-using-stored-procedure.aspx

    http://www.binaryintellect.net/articles/30738a7c-5176-4333-aa83-98eab8548da5.aspx

    I hope it's helpful to you.

    Best Regards,

    Klein zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 27, 2016 2:00 AM

All replies

  • User475983607 posted

    It sounds like you have a design issue related to data relationships. 

    SQL Example

    IF OBJECT_ID('tempdb..#Fields') IS NOT NULL
    	DROP TABLE #Fields
    
    CREATE TABLE #Fields
    (
    	Id	INT,
    	Val1 INT,
    	Val2 INT,
    	Val3 INT,
    	Val4 INT,
    	Val5 INT,
    	Val6 INT,
    )
    
    INSERT INTO #Fields(Id, Val1, Val2, Val3, Val4, Val5, Val6)
    VALUES	(1, 125,125,125,0,0,0),
    		(2, 0,125,125,0,0,0),
    		(3, 0,0,125,0,0,0);
    
    WITH Value_Fields (Id, Val)
    AS
    (
    	SELECT Id, Val1 FROM #Fields
    	UNION ALL
    	SELECT Id, Val2 FROM #Fields
    	UNION ALL 
    	SELECT Id, Val3 FROM #Fields
    	UNION ALL 
    	SELECT Id, Val4 FROM #Fields
    	UNION ALL 
    	SELECT Id, Val5 FROM #Fields
    	UNION ALL 
    	SELECT Id, Val6 FROM #Fields
    )
    
    SELECT Id, COUNT(Val) [Count]
    FROM Value_Fields
    WHERE Val > 0
    GROUP BY Id 
    

    C#/Linq

            public class SomeType {
               public int value1 { get; set; }
               public int value2 { get; set; }
               public int value3 { get; set; }
               public int value4 { get; set; }
               public int value5 { get; set; }
               public int value6 { get; set; }
    
               public static List<int> ConvertToRow(SomeType st)
               {
                   List<int> results = new List<int>();
                   results.Add(st.value1);
                   results.Add(st.value2);
                   results.Add(st.value3);
                   results.Add(st.value4);
                   results.Add(st.value5);
                   results.Add(st.value6);
                   return results;
               }
            }
    
            
            static void Main(string[] args)
            {
                SomeType st = new SomeType() { value1 = 125, value2 = 125, value3 = 125, value4 = 0, value5 = 0, value6 = 0 };
                List<int> resuts = SomeType.ConvertToRow(st);
    
                var values = from i in resuts
                            where i > 0
                            select i;
    
                Console.WriteLine(values.Count());
            }

    Both solutions convert the fields to records (columns to rows) then it's easy to query the results.  The C# code above can be modified to use reflection and generics which would allow for variable fields.

    Tuesday, January 26, 2016 4:17 PM
  • User-986267747 posted

    Hi Adam-M,

    I'm need to execute a LINQ query in EF6 and I can't even figure it out in plain SQL.

    I need to know how many of the six have values greater than 0.

    According to your description, you'd like to execute the query in Entitty Framework , I agree with mgebhard, his suggestion is great, but i suggest that you could use stored procedure to achieve the query, then we could call the stored procedure in Entitty Framework so that we could get the result you want to get.

    First, as mgebhard say, you need to create your table in your database.

    CREATE TABLE YourTable(
    	Id	INT,
    	Val1 INT,
    	Val2 INT,
    	Val3 INT,
    	Val4 INT,
    	Val5 INT,
    	Val6 INT,
    )
    
    INSERT INTO #YourTable(Id, Val1, Val2, Val3, Val4, Val5, Val6)
    VALUES	(1, 125,125,125,0,0,0),
    		(2, 0,125,125,0,0,0),
    		(3, 0,0,125,0,0,0);
    

    Second, you need to create your stored procedure as below.

         CREATE PROCEDURE [dbo].[GetCountThanZero]    
            AS
            BEGIN
    
    		WITH Value_Fields (Id, Val)
    		AS
    		(
    			SELECT Id, Val1 FROM [dbo].YourTable
    			UNION ALL
    			SELECT Id, Val2 FROM [dbo].YourTable
    			UNION ALL 
    			SELECT Id, Val3 FROM [dbo].YourTable
    			UNION ALL 
    			SELECT Id, Val4 FROM [dbo].YourTable
    			UNION ALL 
    			SELECT Id, Val5 FROM [dbo].YourTable
    			UNION ALL 
    			SELECT Id, Val6 FROM [dbo].YourTable
    		)
    
             
            SELECT Id, COUNT(Val) [Count]
    		FROM Value_Fields
    		WHERE Val > 0
    		GROUP BY Id 
            END
    
    
    

    Finally, you could get the result using stored procedure. Like this:

    using (var ctx = new SchoolDBEntities())
        {
            IList<Course> courseList = ctx.GetCoursesByStudentId(1).ToList<Course>();
            //do something with courselist here
        }
    
    public class Course
    {
       public int Id { get; set; }
    
       public int Count { get; set; }
    
    }

    Besides, you could refer to the following links to get more inforamtion and learn how to use stored procedure in ef.

    http://www.entityframeworktutorial.net/data-read-using-stored-procedure.aspx

    http://www.binaryintellect.net/articles/30738a7c-5176-4333-aa83-98eab8548da5.aspx

    I hope it's helpful to you.

    Best Regards,

    Klein zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 27, 2016 2:00 AM