CODEWITHSUNDEEP
linqtelerik-open-access
user650922
user650922

Reputation: 2111

Linq to Group By

I have a function say 

GetGroupByData(DateTime p1,DateTime p2) 
{
    var data = dbContex.tableName
                       .Where(someconditions)
                       .GroupBy(x=>SqlScalarFunction(x.DateStart,p1));
}

     CREATE FUNCTION dbo.SqlScalarFunction(@Date DateTime, @DateFirst DateTime) 
     RETURNS Int 
     AS 
       BEGIN 
        RETURN Cast((cast(@Date - @DateFirst as float) / 7.0) as int) + 1
         END

I am getting an error:

Column 'tableName.DateStart' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Statement(s) could not be prepared.

How can I achieve the desire grouping?

Upvotes: 0

Views: 102

Answers (2)

Doroteya Agayna
Doroteya Agayna

Reputation: 161

An alternative approach you can use to group the objects by the result of a scalar-valued function would be to create a domain method for it, and to consume it in your code as demonstrated below:

using (EntitiesModel dbContext = new EntitiesModel())
{
    var query = from obj in dbContext.MyTableNames
                group obj by EntitiesModel.SqlScalarFunction(someDate, someOtherDate) into objg
                select new
                {
                    MyRange = objg.Key,
                    MyResult = objg
                };
}

If you are using a domain model (an .rlinq file) you can follow the guidelines from this article, in order to create the SqlScalarFunction() domain method. If you have a fluent model, you need to manually add such a method for the function in your context class - the same article will show you the pattern the method has to follow.

Let me know, if this works for you.

Upvotes: 1

user650922
user650922

Reputation: 2111

I solved the issue as 

GetGroupByData(DateTime p1,DateTime p2) 
{
 var function = "dbo.SqlScalarFunction({0}, '" + p1.ToString("yyyy-MM-dd HH:mm:ss") + "')";
   var data = dbContex.tableName.Where(someconditions)
   .GroupBy(x => function.SQL<Int32>(x.DateStartUTC));
}

I found the solution here :http://www.telerik.com/forums/converting-to-datetime-in-where-clause

Upvotes: 1

Related Questions