0

I have two classes like this:

public class Book
{
    public int ID { get; set; }
    public string Name { get; set; }
    public DateTime Date { get; set; }
    public int AuthorID { get; set; }

    public Author author { get; set; }
}

public class Author
{
    public int ID { get; set; }
    public string Name { get; set; }
    public DateTime BirthDate { get; set; }
    public string Place { get; set; }

}

They are linked with Foreign Key (of course AuthorID form Book and ID from Author)

I can retrieve data with this query through LINQ:

var _book = context.Book
    .Where(x => x.ID == ID_I_Pass_From_FrontEnd)
    .Select(x => new Book {
        ID = x.ID, 
        Name = x.Name,
        Date = x.Date, 
        author = new Author {ID = x.author.ID, Name = x.author.Name})
    .FirstOrDefault();

In this way I get data without BirthDate and Place from Author and this is my goal.

Unfortunately I need to use SQL because I'm afraid of performances in case number of data will increase. So I started to code and with this:

var sql = string.Format("SELECT * FROM[Book] AS[x] WHERE([x].ID == ({0})), ID_I_Pass_From_FrontEnd");
var result = context.Book.FromSql(sql).Include(x => x.author).FirstOrDefault();

I can retrieve data but with BirthDate and Place. I'd like to avoid these informations.

Of course my real classes are full of columns, what I wrote in this post is just an example.

Other informations:

  • I'm using AZURE sql as db
  • I tried, in sql string, to insert INNER JOIN for join with Author table but I got error: "Sequence contains more than one matching element". Probably it is confused about same column name (ID in Book and ID in Author).

How can I achieve, with SQL string, same data of LINQ query? so how can I avoid to get data not useful in that moment?

Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
user1106897
  • 87
  • 7
  • 1
    Why do you think Linq would not perform well in case the data would increase? – Magnus Jun 03 '22 at 11:14
  • *how can I avoid to get data not useful in that moment* -- Not by using raw SQL. Why don't you just inspect the generated queries in various scenarios? That will answer the questions you're asking here. – Gert Arnold Jun 03 '22 at 12:20
  • Like I wrote in post, this is just an example. in my real code, I use contains method in where clause like this ...where(x => MyListIDs.Contains(x.ID)) According on what you can read here: https://stackoverflow.com/questions/8107439/why-is-contains-slow-most-efficient-way-to-get-multiple-entities-by-primary-ke it's better to use sql than linq – user1106897 Jun 03 '22 at 23:05

1 Answers1

0

Use the same projection as in pure LINQ query. There is no difference:

var _book = context.Book.FromSql(sql)
    .Select(x => new Book {
        ID = x.ID, 
        Name = x.Name,
        Date = x.Date, 
        author = new Author {ID = x.author.ID, Name = x.author.Name})
    .FirstOrDefault();
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32