Home

How can I call a SQL function in LINQ to SQL using LINQPad?

I have a SQL function with 2 parameters that I can call like this in a SQL select:
member_type_on_date(id, '2020-03-31')
for example:
select id, member_type_on_date(id, '2020-03-31') as member_type
from customers
where id = 112

How can I call that in LINQ?
I tried this:
var query1 = (from c in customers
where c.id == 112
select new
{
c.id,
member_type_on_date(id, "2020-03-31")
}).ToArray();
query1.Dump("ID and Member Type");

But I am getting these errors:
CS0746 Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access.
CS0103 The name 'member_type_on_date' does not exist in the current context

Thanks,
Steven

Comments

  • Expand the tree view for your database on the left. Under 'functions', you should see your SQL function. If you right-click the function, it will provide examples on how you can call i t.

  • Hi Joe,

    I opened the tree on the left, but it only shows Tables and Views. It's the same when I write SQL syntax, but I was still able to call the function that I showed above.
    This is a connection to a MySQL server using the IQ driver.

    Thanks,
    Steven

  • That will be why - functions are not supported with the IQ driver. LINQPad supports functions only with the LINQ to SQL driver.

  • I see. But with the IQ driver it worked with SQL syntax.
    Is there any other driver that I could use to connect to MySQL and use SQL functions with LINQ?
    Thanks,
    Steven

  • You could try the third-party LINQ-to-DB driver - it's possible that it might handle that better.

  • Thanks Joe.
    I tried the LINQ-to-DB driver and I was able to connect, but I found when I enable the option "Include Stored Procedures and Function", I get an error: "Unknown table 'parameters' in information_schema.
    Without the option "Include Stored Procedures and Function", I can expand the tree and it shows the tables and views, but it's the same as with the IQ driver.
    Here is a screenshot with the error:

  • edited July 2020

    That sounds like quite old database. INFORMATION_SCHEMA.PARAMETERS was added to mysql 5.5.3 10 years ago

  • Anyway. If linq2db cannot generate mappings due to lack of metadata tables, you can define mapping yourself like that (edit parameters and return type to match your function)

    [LinqToDB.Sql.Function(Name="member_type_on_date", ServerSideOnly=true)]
    public static string member_type_on_date(int? id, string date) => throw new InvalidOperationException();

  • Thanks Dluk.
    This is a little advanced for me. I wasn't sure where to I put this code.
    So I entered it at the top of my script, and now my script looks like this:

    [LinqToDB.Sql.Function(Name="member_type_on_date", ServerSideOnly=true)]
    public static string member_type_on_date(int? id, string date) => throw new InvalidOperationException();

    var query1 = (from c in customers
    where c.id == 112
    select new
    {
    c.id,
    member_type_on_date(id, "2020-03-31")
    }).ToArray();
    query1.Dump("ID and Member Type");

    But I don't understand why it shwos an error, underlining the "var" keyword at "var query1 ... " and the errors is:
    "CS0825 The contextual keywoard 'var' may only appear within a local variable declaration or in script code"

    Thank you,
    Steven

  • edited July 2020

    Put it to "My Extensions.cs" file

  • Thanks Dluk.

    Am I adding that to the end of that file?:
    void Main()
    {
    // Write code to test your extensions here. Press F5 to compile and run.
    }
    public static class MyExtensions
    {
    // Write custom extension methods here. They will be available to all queries.
    }
    // You can also define non-static classes, enums, etc.
    [LinqToDB.Sql.Function(Name = "member_type_on_date", ServerSideOnly = true)]
    public static string member_type_on_date(int? id, string date) => throw new InvalidOperationException();

    it shows me some errors now there:
    CS0246 The type or namespace name 'LinqToDB' could not be found (press F4 to add a using directive or assembly reference)
    CS0246 The type or namespace name 'Name' could not be found (press F4 to add a using directive or assembly reference)
    CS0246 The type or namespace name 'ServerSideOnly' could not be found (press F4 to add a using directive or assembly reference)

    Do I need to add reference to some .dll files?

  • yes, you need to add reference to linq2db

  • Thanks Dluk.

    I saw that the driver file "linq2db.LINQPad.lpx" is actually a zip file and found the "linq2db.dll" file inside.
    Copied to Program Files\LINQPad folder and added a reference to that.
    Now I don't have those 3 errors anymore, and I only have 1 error now when I try to run the script:
    "Cannot execute text selection: CS0103 The name 'member_type_on_date' does not exist in the current context"
    and highlighting line number 5:

    var query1 = (from c in customers
    where c.id == 112
    select new {
    c.id,
    member_type_on_date = member_type_on_date(id, "2020-03-31")
    }).ToArray();
    query1.Dump("ID and Member Type");

    Thanks a lot for your help,
    Steven

  • As it is not extension method, you need to specify class name: MyExtensions.member_type_on_date

  • Hi Dluk,

    Sorry I still cannot make it working, I'm a bit new to C#.
    So you mean like this?
    var query1 = (from c in customers
    where c.id == 112
    select new {
    c.id,
    member_type_on_date = MyExtensions.member_type_on_date(id, "2020-03-31")
    }).ToArray();
    query1.Dump("ID and Member Type");

    I am till getting an error:
    "Cannot execute text selection: CS0117 'MyExtensions' does not contain a definition for 'member_type_on_date' "

    But I have this already in MyExtensions file and ran it and it compiles:
    void Main()
    {
    // Write code to test your extensions here. Press F5 to compile and run.
    }
    public static class MyExtensions
    {
    // Write custom extension methods here. They will be available to all queries.
    }
    // You can also define non-static classes, enums, etc.
    [LinqToDB.Sql.Function(Name = "member_type_on_date", ServerSideOnly = true)]
    public static string member_type_on_date(int? id, string date) => throw new InvalidOperationException();

    Thanks,
    Steven

  • you should move your method to MyExtensions class:

    // Write custom extension methods here. They will be available to all queries.

  • OK, thanks, I moved it to MyExtensions class now.

    Now I am getting a different kind of error:
    "InvalidOperationException: Operation is not valid due to the current state of the object."
    It's in this line:
    "var query1 = (from c in customers"

    Here is the StackTrace:
    at MyExtensions.member_type_on_date(Nullable1 id, String date) at lambda_method(Closure , FieldReader ) at IQToolkit.Data.DbEntityProvider.Executor.<Project>d__101.MoveNext()
    at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
    at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at IQToolkit.Data.DbEntityProvider.Executor.Execute[T](QueryCommand command, Func2 fnProjector, MappingEntity entity, Object[] paramValues)
    at IQToolkit.Data.EntitySession.SessionExecutor.Execute[T](QueryCommand command, Func2 fnProjector, MappingEntity entity, Object[] paramValues) at lambda_method(Closure ) at IQToolkit.Data.EntityProvider.Execute(Expression expression) at IQToolkit.Data.EntitySession.SessionProvider.Execute(Expression expression) at IQToolkit.Query1.GetEnumerator()
    at System.Linq.Buffer1..ctor(IEnumerable1 source)
    at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
    at UserQuery.Main() in C:\Users\snagy\AppData\Local\Temp\LINQPad5_uwcpnzdn\query_ljblun.cs:line 41
    at LINQPad.ExecutionModel.ClrQueryRunner.Run()
    at LINQPad.ExecutionModel.Server.RunQuery(QueryRunner runner)
    at LINQPad.ExecutionModel.Server.StartQuery(QueryRunner runner)
    at LINQPad.ExecutionModel.Server.<>c__DisplayClass153_0.b__0()
    at LINQPad.ExecutionModel.Server.SingleThreadExecuter.Work()
    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
    at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
    at System.Threading.ThreadHelper.ThreadStart()

    The Source shows: MyExtensions.FW46
    and HResult shows: -2146233079

    Thanks,
    Steven

  • your stack show that you use IQToolkit, not linq2db provider

  • Thanks Dluk, I didn't notice that.
    It works everything now.
    Thanks a lot for your help.
    Steven

Sign In or Register to comment.