How to store and then execute linq2db query?

Question

I'm very new to C#. In other languages I'm used to work with DB in a way "prepare, then execute statement with needed parameters", so I could pass prepared query to function and do all such things. Now I'm trying to solve this task: get almost identicaly result sets from different databases in different contexts. Like you have "files" table with files metadata and in one DB it does have "file_priority" field, and in other DB it does have "file_description" field and so on, but most fields are common and processing logic are almost same.

So I wanted to prepare query in outer code and pass it along with needed param values to data-processing function (or class).

I can't find any example of executing (pass param values to query) linq2db query.

==========UPD============

Ok, example. Let's talk about Perl.

package main1;
use FileProcessor;

my $sth = $dbh->prepare(qq[ SELECT name, extension, file_priority FROM files WHERE file_id = ? ]);

my $processor = new FileProcessor(query => $sth);
my $file_id = $ENV{file_id};
my $file_data = $processor->get_file(id => $file_id);

package main2;
use FileProcessor;

my $sth = $dbh->prepare(qq[ SELECT name, extension, file_description FROM files WHERE file_id = ? ]); # preparing query

my $processor = new FileProcessor(query => $sth);
my $file_id = $ENV{file_id};
my $file_data = $processor->get_file(id => $file_id);

package FileProcessor;

sub new {
    my $class = shift;
    my $self = {@_};
    bless $self, $class;
}

sub get_file {
    my $self = shift;
    my %params = @_;
    $self->{query}->execute($params{$file_id}); #passing params to query
    my $file_data = $self->{query}->fetchrow_hashref; # fetching result from DB
# do something with file data
# ...
    return $file_data;
}

It's unclear what exactly you're looking for. Could you provide an attempt for what you're trying to do? And/or perhaps provide an example in SQL that explains how you want it to be reusable? — Xerillio, Mar 20 '21 at 22:56

score 1 · Answer 1 · answered Mar 22 '21 at 14:42

1

linq2db will not give you preparation step. But you can run raw query via a lot of extensions:

db.Query<File>("SELECT name, extension, file_priority FROM files WHERE file_id = @fileid", 
   new DataPamater("@fileid", id));

db.Query<File>("SELECT name, extension, file_priority FROM files WHERE file_id = @fileid", 
   new { fileid = id });

It is not typical for linq2db to do that in such way, because it supports LINQ queries and parameters will be created automatically:

var id = 1;
var query = 
   from f in db.GetTable<File>()
   where f.file_id = id
   select new File
   {
      name = f.name,
      extension = f.extension,
      file_priority = f.file_priority
   };

var file = query.FirstOrDefault();

answered Mar 22 '21 at 14:42

Svyatoslav Danyliv

21,911
3
16
32

I did some research further. Isn't CompiledQuery.Compile() is what I'm looking for? – Eldhenn Mar 23 '21 at 15:40
No, it is not related. Actually why do you need prepared query? – Svyatoslav Danyliv Mar 23 '21 at 15:42
I... want to pass query to service. From outside. And that service will execute it with given parameters. – Eldhenn Mar 23 '21 at 18:12
So, first example is what you need. – Svyatoslav Danyliv Mar 23 '21 at 19:54
But it is raw SQL. And if I want LINQ, why not to use CompiledQuery.Compile()? – Eldhenn Mar 24 '21 at 08:31
Compiled query is for static query caching. Not for sending via network. Crete question issue in `linq2db` github repository with explanation what you are trying to achieve and we will try to help you. SO not for discussions. – Svyatoslav Danyliv Mar 24 '21 at 09:46

How to store and then execute linq2db query?

1 Answers1