Error: FileNotFoundException: Could not load file or assembly 'System.Data.OleDb, Version=4.0.0.0

nstefi

I had a script in LINQPad 5 that worked fine and loaded an Excel file and did some LINQ to SQL queries from the database looking up values from the Excel file.
I upgraded to LINQPad 6 and now I see an error in this line:
var excel = ExcelProvider.Create(@EmailSearchList.xlsx, "Sheet1");

The errors is this:
FileNotFoundException: Could not load file or assembly 'System.Data.OleDb, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'. The system cannot find the file specified.

I have a reference to this DLL: ScipBe.Common.Office.dll, I think that's what contains the "ExcelProvider".
I don't know where is it trying to locate "System.Data.OleDb", what does this error actually mean and how can I fix it?

Thanks,
Steven

sgmoore

Sounds like you are using a dll which wasn't built for .netcore.

The two easiest solutions would be either find a .netcore version of your ExcelProvider library or stick with LinqPad5.

See https://forum.linqpad.net/discussion/1899/linqpad6-and-referencing-dlls-targeting-older-framework-versions#latest for more info.

nstefi

Thanks @sgmoore.
It looks like it's easier if I just return back to LINQPad 5, but I did that and not sure what I messed up, now I am getting some errors in LINQPad 5 too:
The error is: "Operation is not valid due to the current state of the object" in a line like this:
var a = MyExtensions.encode("101".ToString());

In My Extensions I was trying to call a function from the database, since LINQpad wasn't showing me the database functions in the tree:
public static class MyExtensions
{
[LinqToDB.Sql.Function(Name = "encode", ServerSideOnly = true)]
public static string encode(string contact_id) => throw new InvalidOperationException();
}
Thanks,
Steven

sgmoore

Have you got Include stored Procedure and Functions ticked on your database connection?

Don't know much about LInqToDB, but I suspect the functions would have to work in a similar manner to linq2sql FunctionAttribute in that they can only be used in linq expressions.

For example, if I define

[FunctionAttribute(Name = "ISNUMERIC", IsComposable = true)]
public int IsNumeric(string str) => throw new NotImplementedException();

then I can use this in a linq expression, eg

(from c in Customer where IsNumeric(c.Code) == 1 select c)

but I can't use it outside a linq expression,

eg var a = IsNumber("123")

which would try to execute the C# code on the client, ie throw new NotImplementedException();

nstefi

Thanks @sgmoore.
I cannot include stored procedures or functions in my LinqToDB driver, because I get an error. I had another post about that and with the help of @Dluk I was able to make it working:
https://forum.linqpad.net/discussion/2169/how-can-i-call-a-sql-function-in-linq-to-sql-using-linqpad#latest
That's how I ended up calling the functions through "My Extensions".

I used the function in a linq expression but still got the same error "Operation is not valid due to the current state of the object" and that's why I tried to test it with that line (var a = ...). I removed that test line and somehow it worked again now. But I noticed that at some point the Linq to DB connection was showing a blank line instead of Data Provider and it gave the same error. After picking "MySQL" again it worked.
Strange that in the past for the Linq to DB connection I could fill out some boxes like server, database name, username, etc, but now it's just one box that asks for connection string. Maybe it's a different driver. Is saw one already listed in LINQPad in the window under "Build data context automatically", but I think last time I had to install it from the "View more drivers" button.
Anyway, it works now again, thanks for your help.