Inserting the count value of records into database table using LINQtoSQL

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

Solution

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();