Inserting the count value of records into database table using LINQtoSQL

Question

I have a database table called AllCustomersHistoryOfRecords which is table with history of all the records bought by all the users who visit my application/website. There is another table called Components which just enlists all the components available (for downloading) from my website. Now i want to count all the records available in AllCustomersHistoryOfRecords and update the count in field *Total_Downloads* of Components table. Here is what i'm doing to accomplish this:

Which is primarily counting the occurences of each record in AllCustomersHistoryOfRecords table.

var componentCount = from c in db.Components
                                 join cr in db.AllCustomersHistoryOfRecords
                                 on c.Component_Name equals cr.Software_Title into temporary
                                 select temporary.Count();

And this is the code i'm using to insert the data into Components table:

Component comp = new Component { Total_Downloads = componentCount};
db.Components.InsertOnSubmit(comp);

But the problem is i'm getting the following error:

Cannot implicitly convert type 'System.Linq.IQueryable<int>' to 'int?'

How can i solve this problem ? Please Help me out!!

Thanks in anticipation

Answer 1

Should there not be a set of parentheses there?

var componentCount = (from c in db.Components
                             join cr in db.AllCustomersHistoryOfRecords
                             on c.Component_Name equals cr.Software_Title into temporary
                             select temporary).Count();

EDIT: If I understand correctly, you are trying to get the sum all all the counts? If correct then how about this:

var componentCount = (from c in db.Components
                             join cr in db.AllCustomersHistoryOfRecords
                             on c.Component_Name equals cr.Software_Title into temporary
                             select temporary.Count()).Sum();

If not, then can you please describe what you want to do?

Answer 2

I'm guessing the componentCount field is a nullable field?

If this is the case you need to cast componentCount to a nullable int and also return a result set from the linq query rather than an IQueryable.

    var componentCount = (from c in db.Components
                                     join cr in db.AllCustomersHistoryOfRecords
                                     on c.Component_Name equals cr.Software_Title into temporary
                                     select c).Count();
    Component comp = new Component { Total_Downloads = (int?)componentCount};
    db.Components.InsertOnSubmit(comp);

EDIT Looping through components and updating counts

You'll need to replace c.ComponenetId and comp.ComponentId with what ever is the primary key on the component table/object. There may be some minor issues as I havent run this but it should give you a good idea of how to achieve what you are after.

var components = (from c in db.components select c).ToList();
foreach(var comp in components)
{
    var componentCount = (from c in db.Components
                         join cr in db.AllCustomersHistoryOfRecords
                         on c.Component_Name equals cr.Software_Title into temporary
                         where c.ComponentId == comp.ComponentId
                         select c).Count();
    comp.Total_Downloads = (int?)componentCount;                
}
db.SubmitChanges();

Answer 3

Have you tried calling .First() on your query so the result is not returned as an IQuerable. This is also stated in this post.

Cannot implicitly convert type 'System.Linq.IQueryable' to 'int?'