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

nstefi

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

JoeAlbahari

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.

nstefi

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

JoeAlbahari

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

nstefi

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

JoeAlbahari

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

nstefi

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:

Dluk

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

Dluk

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();

nstefi

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

Dluk

Put it to "My Extensions.cs" file

nstefi

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?

Dluk

yes, you need to add reference to linq2db

nstefi

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

Dluk

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

nstefi

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

Dluk

you should move your method to MyExtensions class:

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

nstefi

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

Dluk

your stack show that you use IQToolkit, not linq2db provider

nstefi

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