interesting behaviour of linq

[aldm]

Hi,

I'm working on one project in .NET 4 and I use LINQ for database access. I have a problem.
Consider a following code:

Guid userId = new Guid(Membership.GetUser().ProviderUserKey.ToString());
            IEnumerable<ViewCount> views = from vc in dbContext.ViewCounts
                                           where vc.Time >= dateFrom && vc.Time <= dateTo
                                           group vc by new { catalogueId = vc.Catalogue_Id, day=new DateTime(vc.Time.Year, vc.Time.Month, vc.Time.Day) } into vc2

                                           join c in dbContext.CatalogueItems on vc2.Key.catalogueId equals c.Id
                                           join p in dbContext.Products on c.Product_Id equals p.Id
                                           join s in dbContext.Stores on c.Store_Id equals s.Id

                                           where s.User_Id == userId

                                           select new ViewCount
                                           {
                                               Id = 0,
                                               Catalogue_Id = vc2.Key.catalogueId,
                                               Product_Id = vc2.Count(),
                                               IP_Address = p.Name,
                                               Time = DateTime.Now
                                           };

When I run this code it shows me error "Only parameterless constructors and initializers are supported in LINQ to Entities." in execution time.
It seems problem is in grouping by day of view - when I remove this part

day=new DateTime(vc.Time.Year, vc.Time.Month, vc.Time.Day)

it works OK. But I really need a grouping by day (not time but jost a day). I know there is other ways I can do this but this is the most elegant and I really want to know what is problem here.
Class dbContext is inherited from DBContext class.

Can you please help me?

Thanks in advance

[skatamatic]

Try using the Let keyword to create a temp datetime.

var views = from vc in dbContext.ViewCounts
            let tempDate = new DateTime(vc.Time.Year, vc.Time.Month, vc.Time.Day)
            where vc.Time >= dateFrom && vc.Time <= dateTo
            group vc by new { catalogueId = vc.Catalogue_Id, day=tempDate } into vc2...

I haven't tested it but I think that's what I did to solve a similar problem a while ago.

[aldm]

Hi,

Thanks for reply.
I tried this before I ask question but it doesnt work either.
Is there any other way ?