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.

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

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:

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.

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