Innerjoin in Linq-to-sql for this in asp.net mvc?

Question

I use asp.net mvc... How to write an inner join in linq-to-sql for this sql query

select M.Mat_id,M.Mat_Name,T.Name as Measurement,M.Mat_Type as Description 
 from Material as M inner join MeasurementTypes as T 
  on M.MeasurementTypeId = T.Id where M.Is_Deleted=0

And my repository class has this,

    public class ConstructionRepository
    {
        private CRDataContext db = new CRDataContext();
        public IQueryable<Material> FindAllMaterials()
        {
            return db.Materials;
        }
    }

My result db.Materials is the table data.. I dont want that i want to innerjoin with another table and show the data....

public class ConstructionRepository
 {
    private CRDataContext db = new CRDataContext();
     public IQueryable<Material> FindAllMaterials()
    {
                //inner join query
     }
    }

Any suggestions...

Answer 1

This will return anonymous types with the details you've specified in your SQL query but you may prefer to simply return the material objects themselves.

from material in db.Materials
from measurementType in material.MeasurementTypes
where material.Is_Deleted = false
select new {
    material..Mat_id,
    material.Mat_Name,
    Measurement = measurementType.Name,
    Description = material.Mat_Type
}