LINQ error

[Michael]

With This code :

private List<Album> GetTopSellingAlbums(int count)
{
    // Group the order details by Album and return
    // the albums with highest count
    return storeDB.Albums
        .OrderByDescending(a => a.OrderDetails.Count())
        .Take(count)
        .ToList();
}

I have this exception :

System.InvalidOperationException was unhandled by user code
  Message=An exception occured during the execution of '
Extent<MyMVC.Album>.OrderByDescending(a => a.OrderDetails.Count()).Take(1)'. See InnerException for more details.

  Source=Telerik.OpenAccess.35.Extensions
  StackTrace:
       at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single)
       at Telerik.OpenAccess.Query.ExpressionExecution.PerformDatabaseQuery[T](Piece`1 piece, Object[] grpVals)
       at Telerik.OpenAccess.Query.Piece`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at MyMVC.Controllers.HomeController.GetTopSellingAlbums(Int32 count) in C:\Users\Michael\Documents\Visual Studio 2010\Projects\MyMVC\MyMVC\Controllers\HomeController.cs:line 29
       at MyMVC.Controllers.HomeController.Index() in C:\Users\Michael\Documents\Visual Studio 2010\Projects\MyMVC\MyMVC\Controllers\HomeController.cs:line 19
       at lambda_method(Closure , ControllerBase , Object[] )
       at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
       at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<InvokeActionMethodWithFilters>b__a()
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
  InnerException: System.NullReferenceException
       Message=Object reference not set to an instance of an object.
       Source=Telerik.OpenAccess.35.Extensions
       StackTrace:
            at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQueryImpl(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single)
            at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single)
       InnerException:

It's port of MvcMusicStore database from MSSQL to PostgreSQL database.

[Michael]

Strange, above query dont work, but this work :

private List<Album> GetTopSellingAlbums(int count)
{
    // Group the order details by Album and return
    // the albums with highest count
    var query = from c in storeDB.Albums
                .Take(count)
                orderby c.Carts.Count() descending
                select c;
    return query.ToList();
}

 

 

The question is why is this so?

Michael.

 

[Thomas]

Hello Michael,

can you give us the inner exception - it will provide us with more hints why this occurs.
Your workaround is semantically not equivalent, as the Take happens logically before the ordering is performed. Just think what linq2objects will do to the data...
 
Greetings,
Thomas
the Telerik team

[Michael]

Inner exception : {"Object reference not set to an instance of an object."}

Table definitions :

CREATE TABLE "public"."Order" (
  "OrderId" SERIAL,
  "OrderDate" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  "Username" VARCHAR(256),
  "FirstName" VARCHAR(160),
  "LastName" VARCHAR(160),
  "Address" VARCHAR(70),
  "City" VARCHAR(40),
  "State" VARCHAR(40),
  "PostalCode" VARCHAR(10),
  "Country" VARCHAR(40),
  "Phone" VARCHAR(24),
  "Email" VARCHAR(160),
  "Total" NUMERIC(10,2) NOT NULL,
  CONSTRAINT "PK__Invoice__D796AAB51A14E395" PRIMARY KEY("OrderId")
) WITH OIDS;

and :

CREATE TABLE "public"."OrderDetail" (
  "OrderDetailId" SERIAL,
  "OrderId" INTEGER NOT NULL,
  "AlbumId" INTEGER NOT NULL,
  "Quantity" INTEGER NOT NULL,
  "UnitPrice" NUMERIC(10,2) NOT NULL,
  CONSTRAINT "PK__InvoiceL__0D760AD91DE57479" PRIMARY KEY("OrderDetailId"),
  CONSTRAINT "FK_InvoiceLine_Album" FOREIGN KEY ("AlbumId")
    REFERENCES "public"."Album"("AlbumId")
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT "FK__InvoiceLi__Invoi__2F10007B" FOREIGN KEY ("OrderId")
    REFERENCES "public"."Order"("OrderId")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITH OIDS;

CREATE INDEX "AlbumId_idx_OrderDetail" ON "public"."OrderDetail"
  USING btree ("AlbumId");
CREATE INDEX "OrderId_idx_OrderDetail" ON "public"."OrderDetail"
  USING btree ("OrderId");

and this table also :

CREATE TABLE "public"."Order" (
  "OrderId" SERIAL,
  "OrderDate" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  "Username" VARCHAR(256),
  "FirstName" VARCHAR(160),
  "LastName" VARCHAR(160),
  "Address" VARCHAR(70),
  "City" VARCHAR(40),
  "State" VARCHAR(40),
  "PostalCode" VARCHAR(10),
  "Country" VARCHAR(40),
  "Phone" VARCHAR(24),
  "Email" VARCHAR(160),
  "Total" NUMERIC(10,2) NOT NULL,
  CONSTRAINT "PK__Invoice__D796AAB51A14E395" PRIMARY KEY("OrderId")
) WITH OIDS;

[Thomas]

Hello Michael,

ok, the Albums table was missing but I emulated one. And then the Carts table was missing...
I think you could rewrite the query as follows:

var query = (from c in ctx.OrderDetails
                        group c by c.Album into g
                        orderby g.Count() descending
                        select g).Take(count);
            foreach (var z in query)
                Console.WriteLine(z.Key.Name);

Hope this helps,
Thomas
the Telerik team

[Michael]

Hi Thomas,

Now i see 2 SQL scripts for Order table.
I can't attach create script so you need do download from here : http://infotechnologies.rs/Create.txt
I tried your example, but this is not what I need because View is strongly-typed and need to be Album based. This is exception that I got :

Error   1   Cannot implicitly convert type 'System.Collections.Generic.List<System.Linq.IGrouping<MyMVC.Album,MyMVC.OrderDetail>>' to 'System.Collections.Generic.List<MyMVC.Album>'    C:\Users\Michael\Documents\Visual Studio 2010\Projects\MyMVC\MyMVC\Controllers\HomeController.cs    45  20  MyMVC

Thanks, Michael

[Thomas]

Hi Michael,

ok, got the exception now. I will try to fix this as soon as possible. Until it is fixed, you can temporarily use the query that you posted earlier. I will notify you once it is fixed. The problem is the generation of the group by count query, a table alias is not present when needed.

I have updated your Telerik Points. 

Best wishes,
Thomas
the Telerik team