CODEWITHSUNDEEP
c#entity-frameworklinqasp.net-web-api
rst
rst

Reputation: 2724

Reading either byte[] or float with Linq to Entities

I have table with one column being binary and another being float. I need to select the following using SQL

SELECT ISNULL(myBinary, myFloat) FROM table

That works and I get one column with all binary

0x3F800000
0xE5C13DBAB611123B47A7
0x9946C3BA9946C3BA9946
0xDE0E1D3C8B7A143C6DB7
0x3F800000

etc.

Now I would like to make this query using Linq to Entities, however I just can't find a code that would compile

context.table.select(s => new MyObject()
  { 
     Result = s.myBinary ?? s.myFloat // <--- '??' operator cannot be applied to operands of type 'byte[]' and 'float'
  });

 class MyObject { public Object Result {get; set;} }

How do I get those values? BitConverter doesn't work either (on the float)

UPDATE

Why I'm asking: so if I were to select both columns seperately, I get more execution time

set statistics time on
SELECT TOP (5000) ISNULL([x], [y])
  FROM [table];
set statistics time off
set statistics time on
SELECT TOP (5000) [x], [y]
  FROM [table];
  set statistics time off

yields (even executing it multiple time, it's always around the same)

(5000 rows affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 7 ms.

(5000 rows affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 113 ms.

Note: In one comment I wrote factor 40, that was with another measurement method. The statistics time yields about factor 10.

If I increase the amount of rows, I get

(50000 rows affected)

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 820 ms. (with ISNULL)

(50000 rows affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 1365 ms.

and with less rows (about 1000 or less), the execution time is not measurable equally, so about 1 ms. BUT: I expect about presicely 5000 to 10000 rows per query.

Upvotes: 2

Views: 445

Answers (3)

Ivan Stoev
Ivan Stoev

Reputation: 205769

What I can offer as solution is a custom store function, mapped to the built-in ISNULL SQL function.

If you were using Code First model, you would need EntityFramework.Functions package.

But since you are using edmx, the process is a bit more complicated and requires manual editing of the edmx file. The technique is partially covered by the How to: Define Custom Functions in the Storage Model MSDN topic.

Open your edmx file with XML (text) editor. Locate the Schema sub element of the edmx:StorageModels element and add the following inside:

<Function Name="IsNull" BuiltIn="true" IsComposable="true" ReturnType="binary">
  <Parameter Name="expr1" Type="binary" />
  <Parameter Name="expr2" Type="float" />
</Function>

Note that as mentioned in the MSDN link:

Changes made to the SSDL section of an .edmx file, as suggested in the procedure below, will be overwritten if you use the Update Model Wizard to update your model.

so make sure to keep this in some safe place and re-include it if you update the edmx from the database.

Then add a method in some static class and decorate it with the DbFunction attribute:

public static class CustomDbFunctions
{
    const string Namespace = "EFTest.MyDbContextModel.Store";

    [DbFunction(Namespace, "IsNull")]
    public static byte[] IsNull(byte[] expr1, double expr2) => throw new NotSupportedException();
}

(update the Namespace string to match the value of the Namespace attribute of the <edmx:StorageModels><Schema> element).

And that's all. Now you should be able to use the above function in your LINQ to Entities queries:

class MyObject { public byte[] Result { get; set;} }

context.MyTable.Select(e => new MyObject
{ 
    Result = CustomDbFunctions.IsNull(e.myBinary, e.myFloat)
});

and EF6 will happily translate it to the desired SQL ISNULL function.

Upvotes: 3

koshy
koshy

Reputation: 1

Please change your class definition to accept null values.

public class FloatOrByte 
{
    public byte[] MyBinary { get; set; }
    public float? MyFloat { get; set; }
}

Then fetch values using your DBContext class. The problem is EF can't translate the null-coalescing operator to SQL.

var result = context.table.Select(s => s);

var processResult = result.Tolist().Select(s=> new MyObject{

    Result = (s.MyBinary == null) ? (object)s.MyFloat : (object)s.MyBinary 

 });

Upvotes: 0

DavidG
DavidG

Reputation: 119076

The problem with the way you are trying to get this to work is that you would need to resort to some nasty hacks to determine if your return value was a byte[] or a float. That's why you've been trying to get the EF query to return object. It may be possible to get it working by hacking the float into a byte[], but I would suggest a far simpler and more logical approach: Return both values and let the app decide what to do. For example, return something like this object:

public class FloatOrByte // Do not call it this!
{
    public byte[] MyBinary { get; set; }
    public float MyFloat { get; set; }
}

And return it like this:

var result = context.table.Select(s => new FloatOrByte
{ 
    MyBinary = s.myBinary,
    MyFloat = s.myFloat
};

Now you can check the value for null:

if(result.MyBinary != null)
{
    // Do stuff with the byte value
}
else
{
    // Do stuff with the float value
}

Upvotes: 2

Related Questions