get count from Iqueryable<T> in linq-to-sql?

Question

The following code doesn't seem to get the correct count.....

 var materials = consRepository.FindAllMaterials().AsQueryable();
 int count = materials.Count();

Is it the way to do it.... Here is my repository which fetches records...

public IQueryable<MaterialsObj> FindAllMaterials()
        {
           var materials =  from m in db.Materials
            join Mt in db.MeasurementTypes on m.MeasurementTypeId equals Mt.Id
                   where m.Is_Deleted == 0
                   select new MaterialsObj()
                   {
                       Id = Convert.ToInt64(m.Mat_id),
                       Mat_Name = m.Mat_Name,
                       Mes_Name = Mt.Name,
                   };
            return materials;

        }

Edit:

when i use this,

        var materials = consRepository.FindAllMaterials().AsQueryable();
        return View("Materials", materials);

I get 18 rows in my table... So y cant i get the count as 18 instead it gives 12

Ans:

Breakpoint doesn't seem produce me the result but response.Write(count) did...

Answer 1

Is it possible that you're not joining on the correct columns? The only reason I ask is that your id columns aren't consistently named in each class. For Materials you are using Mat_id, but in MeasurementTypes you are using simply, Id. It makes me wonder if you're trying to join a natural key value against an artificial primary key instead of the corresponding natural foreign key.

Answer 2

This should get the correct count:

int count = consRepository.FindAllMaterials().Count();

How are you iterating through the model in your view?

Is it possible that you are displaying duplicate entries?