How can I set a timeout om my query on LinqPad on ExecuteQueryDynamic?

Question

How can I set a timeout om my query on LinqPad on ExecuteQueryDynamic?

Util.CurrentDataContext.ExecuteQueryDynamic($"SELECT count(*) FROM MyDb.dbo.{viewName}");

I would like to use this timeout to detect the too slow views. For each view I request a count but when my count take too much time I just stop it and count the next one.

Here is my full LINQPad code

void Main()
{
    // In case of error "There is already an open DataReader associated with this Command which must be closed first."
    // https://stackoverflow.com/q/6062192/196526
    // Add MultipleActiveResultSets=true to connection string. 

    var biewsCount = b.V_sysobjects.Where(v => v.Type == "V").Count();
    var bViewsDetail = Util.OnDemand<List<SysObject>>("Get Views Detail", () => GetViewsDetail("b", b.V_sysobjects.Where(v => v.Type == "V").Select(v => v.Name).ToList()));
    bViewsDetail.Dump();
}

public List<SysObject> GetViewsDetail(string database, List<string> objectNames)
{
    var result = new List<SysObject>();
    foreach (var name in objectNames)
    {
        Console.Write($"{database}.dbo.{name}");
        // I should add a timeout stuff here or before
        var count = (int)Util.CurrentDataContext.ExecuteQueryDynamic($"SELECT count(*) FROM {database}.dbo.{name}").Select(y => y).Single();
        Console.Write($" {count}\n");
        result.Add(new SysObject
        {
            Database = database,
            ObjectName = name,
            Rows = count
        });
    }
    return result;
}

public class SysObject
{
    public string Database { get; set; }
    public string ObjectName { get; set; }
    public string ObjectType { get; set; }
    public string IndexName { get; set; }
    public long Rows { get; set; }
    public long? TotalPages { get; set; }
    public long? UsedPages { get; set; }
    public long? DataPages { get; set; }
    public long? TotalSpaceMB { get; set; }
    public long? UsedSpaceMB { get; set; }
    public long? DataSpaceMB { get; set; }
}

Answer 1

This is a bug in LINQPad. The ExecuteQueryDynamic method should honor the DataContext's CommandTimeout.

This has now been fixed (as of 5.37.4, currently in beta).

Note that Util.CurrentDataContext is redundant unless the containing method is static. The Select(y=>y) is also redundant. So in your example, you can just do this:

CommandTimeout = 1;
var count = (int)ExecuteQueryDynamic($"SELECT count(*) FROM {database}.dbo.{name}").Single();

Answer 2

As far as I know, apart from setting the command or connection timeouts in the client, there is no way to change timeouts on a query in the server.

You can try this way, that works fine for me:

Just before running your actual query, set it for your query

SET LOCK_TIMEOUT 1000;   --1 second

and after running your query set it back to original value. The default value for running query is 600 seconds.