Optimize LINQ query that runs fast in Sql server?

By running SingleOrDefault() you execute the query and have to deal with results in memory after that. You need to stay with IQueryable until your query is fully constructed.

The easiest way to answer "how many child records this parent record has" is to approach it from the child side:

using (var dx = new MyDataContext()){    // If you have an association between the tables defined in the context    int count = dx.Related_Huge_Table_Datas.Where(t => t.MainTable.id == 42).Count();    // If you don't    int count = dx.Related_Huge_Table_Datas.Where(t => t.parent_id == 42).Count();}

If you insist on the parent side approach, you can do that too:

using (var dx = new MyDataContext()){    int count = dx.MainTables.Where(t => t.id == 42).SelectMany(t => t.Related_Huge_Table_Datas).Count();}

If you want to keep a part of this query in a function like tblInfo, you can, but you can't instantiate MyDataContext from inside such function, otherwise you will get an exception when trying to use the query with another instance of MyDataContext. So either pass MyDataContext to tblInfo or make tblInfo a member of partial class MyDataContext:

public static IQueryable<MainTable> tblInfo(MyDataContext dx, int id){    return dx.MainTables.Where(t => t.id == id);}...using (var dx = new MyDataContext()){    int count = tblInfo(dx, 42).SelectMany(t => t.Related_Huge_Table_Datas).Count();}

Try this

MyDataContext context = new MyDataContext();var count=context.Related_Huge_Table_Data.where(o=>o.Parentid==id).Count();//orint count=context.Database.SqlQuery<int>("select count(1) from Related_Huge_Table_Data where Parentid="+id).FirstOrDefault();

If you wish to take full advantage of your SQL Database's performance, it may make sense to query it directly rather than use Linq. Should be reasonably more performent :)

 var Related_Huge_Table_Data = "TABLENAME";//Input table name here var Id = "ID"; //Input Id name here      var connectionString = "user id=USERNAME; password=PASSWORD server=SERVERNAME; Trusted_Connection=YESORNO; database=DATABASE; connection timeout=30"; SqlCommand sCommand = new SqlCommand(); sCommand.Connection = new SqlConnection(connectionString); sCommand.CommandType = CommandType.Text; sCommand.CommandText = $"COUNT(*) FROM {Related_Huge_Table_Name} WHERE Id={ID}"; sCommand.Connection.Open(); SqlDataReader reader = sCommand.ExecuteReader(); var count = 0; if (reader.HasRows) {     reader.Read();     count = reader.GetInt32(0); } else {      Debug.WriteLine("Related_Huge_Table_Data: No Rows returned in Query."); } sCommand.Connection.Close();