MSSQL: Can a C# function implement a view?

Question

For the existing application that uses the MS SQL server, is it possible to implement a C# functionality that would be mapped as an SQL table?

In other words, can a C# functionality be queried from a legacy SQL query so that it acted as if the SQL table was there?

The motivation: I have blobs with heavily compressed data (archived as files elsewhere). I would like to replace the huge existing SQL table so that the data would be extracted from the archive in the background.

Think as if the SQL table was a cache memory that could be queried as a normal table. When there is a cache miss, the data is extracted by a C# code to fill the missing data in the cache, and only then the real SQL query is performed.

The operation is not time critical. This way even a simpler solution is acceptable -- when the SQL query appears, the arguments are somehow passed to a C# function that fill the empty table with the extracted data, and then the SQL query is performed and the data is returned.

Is anything like that possible at MS SQL server?

Maybe you look something like: stackoverflow.com/questions/13420305/… — VikciaR
– VikciaR, Commented Feb 13, 2017 at 8:46
To add: just use built in FILESTREAM functionality, there is no need to make custom CLR implementation. — VikciaR
– VikciaR, Commented Feb 13, 2017 at 8:47
I see.The only thing I can think about would be a .net program called by a sql programmed task (o maybe using a trigger?) that could keep an updated view with all the transformations you need.... — Pikoh
– Pikoh, Commented Feb 13, 2017 at 14:19
I would recommend then using Table Valued function hidden by View. Then usage would be select * from myView; and your legacys software requirements, I think, will be fulfilled. I have some doubts about performance of this solution, but you can try. — VikciaR
– VikciaR, Commented Feb 13, 2017 at 14:41
BTW, some time ago I asked similar question: stackoverflow.com/questions/31625273/… Some people thinks, that this type of problem is uncommon - question has rating -1 :-) — VikciaR
– VikciaR, Commented Feb 13, 2017 at 14:43

Tomas Chabada · Accepted Answer · 2017-02-13 09:09:03Z

0

You can achieve that using partial class.

public partial class MyTable
{
    public byte[] HeavilyCompressedData
    {
        get
        {
            //loading and returning large data
        } 
    }
}

Remember that you can't write a condition in LINQ on this property (The specified type member is not supported in LINQ to Entities), but I think you don't want to do that.

answered Feb 13, 2017 at 9:09

Tomas Chabada

3,0391 gold badge20 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pepr Over a year ago

Thanks for trying to help. But I am aware of the possibility to call C# code from SQL. This is the reason why I am thinking about implementation in C#. The question is whether the call can be kind of transparently hooked into existing SQL queries. In other words if the SELECT * FROM xxx can call the code internally. That is, if the xxx content can be obtained from the code instead of getting it from a table. (Say, xxx is a view.)

Collectives™ on Stack Overflow

MSSQL: Can a C# function implement a view?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related