LINQPad 5 (Free Edition) : SqlFunctions and C# statement query

aas

Hello,

Is there any clear guidance on how to rely on SqlFunctions class methods (Checksum for instance) in a C# statement query?
Google search results are not really helpful for this matter so I'm asking on this forum board.

The main issue is I'm trying to rely on a Linq-To-Entities feature function while being out of DbContext (or DbSet) when running it in LINQPad.

var result = (from rec in recordTable
select new
{
ProjectionPpty1 = rec.PptyA,
ProjectionPpty2 = SqlFunctions.Checksum( rec.PptyA, rec.PptyB )
})
.AsQueryable();
Kind regards,

Thanks again,

JeffMercado

What type is recordTable? Those sql functions are meant to be run on the db server so if the query isn't sourced by one, you cannot use that function.

aas

Hello,

In my DAL C# library, the EF6 DbContext-derived class is owning a System.Data.Entity.DbSet ( of FDTLine entities ) property named 'FDTLines' which represents the physical recordTable in this ORM.

LinqToEntities query :

var linqToEntitiesQuery = (from fdtL in dbCtx.FDTLines group fdtL by [...] into grp select new {
LineKey = grp.Key,
LineChecksum = SqlFunctions.Checksum(grp.Key.FDTLineID.ToString(), ((grp.All(x => x == null)) ? -1 : grp.Count()).ToString())
}).AsQueryable()

When I'm trying to translate this query from Visual Studio into LinQPad for unit testing purpose (so I dont need to fire the whole application to toy with one query), I can de-refer the DbContext reference and use the recordTable object created in LinQPad object explorer and thus write the following C# expression.

var result = (from line in FDTLines ... group line by [...] into rec
select new
{
ProjectionPpty1 = rec.PptyA,
ProjectionPpty2 = SqlFunctions.Checksum( rec.PptyA, recComputedPropertyB )
})
.AsQueryable();

The translated query does not work in LinQPad becaus it seems to require one extra DLL reference to make SqlFunctions.Checksum method work.

Is there any way to achieve this in LinQPad ?

sgmoore

I'm not sure what you mean by I can de-refer the DbContext reference and use the recordTable object created in LinQPad object explorer

Does that mean you are using the Default Linq to SQL connection? If so then I don't think that will work. You would need to select 'Use a typed data content from your own assembly and point it to your EF dll.

Then add a reference to System.Data.Entity.dll and add the namespace System.Data.Objects.SqlClient

Note, I think this should work, but I can't test this because I don't have a EF6 typed data context dll that I can use and SqlFunctions does not work with EF Core.

aas

@sgmoore said:
Does that mean you are using the Default Linq to SQL connection? If so then I don't think that will work. You would need to select 'Use a typed data content from your own assembly and point it to your EF dll.

Yes, sorry not to have specified this on my issue description ! I thought it was obvious!
So, I'm using the following LinQPad new connection setup option : **Build data context automatically : Default (LINQ to SQL). **
That's exactly what I was trying to convey when talking about 'De-Referring DbContext' (LinQPad connection is not relying on Linq-To-Entities assemblies but on Linq-To-SQL built-in assemblies)

Anyway thanks, I'll give the 2nd option a try ('Use a typed data context from your own assembly') by adding the
complementary external DLL reference and adapting the query to make it work with my own EF 6.4.4 DbContext derived class of my custom .NET C# DAL assembly.

I'll post back when I'll get results,

Kind regards,

aas

Hello,

Results are still negative for my case even after switching to typed data context from my own assembly and using "Query properties" panel features :
1. Additional References : adding "System.Data.Entity.dll" reference
2. Additional Namespace imports : adding "System.Data.Objects.SqlClient"

So, long story short, query result is :

NotSupportedException
**This function can only be invoked from LINQ to Entities. **

JeffMercado

You cannot call it directly like that, it has to be within a IQueryable<T> query. Try calling it like so:

var checksum = context.SomeTable.Select(_ => SqlFunctions.Checksum("A","B")).First();
checksum.Dump();

aas

So like this ?
Cause you're using an uninstantiated context variable in your query so I was wondering...

using( var context = new MyDerivedDbContext() )
{
  var result = context.SomeTable.Select(x => SqlFunctions.Checksum("A","B")); 
  result.Dump();  
}

Still getting :

**NotSupportedException **
LINQ to Entities does not recognize the method 'System.Nullable`1[System.Int32] Checksum(System.String, System.String)' method, and this method cannot be translated into a store expression.