ASP.NET Dynamic Data

Re: Entity Sql - Count function

gameover88 — Wed, 18 Nov 2009 09:34:31 GMT

well it's a little bit late but thanks a lot for your help :)

Re: Entity Sql - Count function

andreadottor — Fri, 03 Jul 2009 13:10:42 GMT

Is normal the type is a generic list, because you execute a select that can return many values.

In a my application (that I have a similar situation) I have create a method that check that the list is not empty and return a value (that is the first element).

In you case, the code can be this:

protected int GetCount(IList<int> count)
{
if (count == null || count.Count < 1)
return 0;

return count[0];
}

or check in debug the correct type.

Re: Entity Sql - Count function

gameover88 — Fri, 03 Jul 2009 12:55:33 GMT

andreadottor:

I don't have try, but in your query missed "as c" near Comments, and you must indicate the name of the class of your ObjectContext

i can't believe how i forgot this.) thanks. now i'm writing this statement:

SELECT m.MediaTitle, (Select COUNT(c.MediaID) FROM MediaPortalEntities.Comments AS c WHERE c.MediaID = m.MediaID) AS TotalComments FROM MediaPortalEntities.Medias AS m

But during the run time this is what it shows in the datalist:

MediaTitle: Fight Club TotalComments: System.Collections.Generic.List`1[System.Data.Common.DbDataRecord]
MediaTitle: The Dark Knight TotalComments: System.Collections.Generic.List`1[System.Data.Common.DbDataRecord] .. still can't get the count numbers :S

MediaTitle: Fight Club
TotalComments: System.Collections.Generic.List`1[System.Data.Common.DbDataRecord]

MediaTitle: The Dark Knight
TotalComments: System.Collections.Generic.List`1[System.Data.Common.DbDataRecord]

still can't get the count numbers :S

Re: Entity Sql - Count function

andreadottor — Fri, 03 Jul 2009 12:17:06 GMT

I don't have try, but in your query missed "as c" near Comments, and you must indicate the name of the class of your ObjectContext

SELECT
m.MediaTitle,
m. SuggestedBy,
m.Summary,
(Select COUNT(c.MediaID) FROM YourObjectContext.Comments AS c WHERE c.MediaID = m.MediaID) AS TotalComments
FROM YourObjectContext.Medias AS m

Entity Sql - Count function

gameover88 — Fri, 03 Jul 2009 10:17:42 GMT

Hi. i'm trying to write an e-sql statement that retrieves all records in a table called 'Medias' and also, number of the comments those belong to each Media, by MediaID (in the Comments table). i've tried several statements like this but i always get error:

SELECT m.MediaTitle, m. SuggestedBy, m.Summary, (Select COUNT(c.MediaID) FROM Comments WHERE c.MediaID = m.MediaID) AS TotalComments FROM Medias AS m

i know this may sound easy but i'm in trouble with this :(. i would appreciate if you could help. thanks a lot.