Join a SQL table with data from API using EF

Question

I've this structure:

• A SQL database for the notes and other data tables (this seems not relevant for this question). The note table got a reference to a ticket. For this, I'm using Entity Framework to load the data.
• An API call for the tickets, for which I'm using the HttpClient from C#.

Now I want to join these two objects with each other using a SQL query to query on multiple other related data from a note.

In SQL I know that you could do something like this. Declaring a table as a variable, insert some data and join it with the notes.

declare @tickets table(
    ID int primary key, 
    [Subject] nvarchar(max)
);

-- Making API call and insert results into @tickets

select *
from Notes note inner join @tickets tick on tick.ID = note.TicketID
where tick.Subject like @q + '%';

My question is now, could I get the same result in EF? I know that I couldn't make an API call using EF, because of this I do this in the method TicketService.GetTicketsAsync().

---

I've tried code below so I could query on the other data tables too.

TicketService ticketService = new TicketService();
IEnumerable<Ticket> tickets = await ticketService.GetTicketsAsync();

(await db.Notes
         .ToListAsync())
         .Join(
             tickets,
             note => note.TicketID,
             tick => tick.ID,
             (note, tick) =>
             {
                 note.Ticket = tick;
                 return note;
             }
         )
         .AsQueryable();

But this gives me this error:

The source IQueryable doesn't implement IDbAsyncEnumerable<Note>. Only sources that implement IDbAsyncEnumerable can be used for Entity Framework asynchronous operations. For more details see http://go.microsoft.com/fwlink/?LinkId=287068.

I've visited the link but the example there uses unit testing and mock data. In my case, it's not testing code and real data.

The code looks also as it isn't performant because using await db.Notes.ToListAsync() would select everything from the Notes table.

---

Using this db.Notes.Join(/* same arguments as second code block */) gives me this compilation error:

A lambda expression with a statement body cannot be converted to an expression tree.

---

With a mapping class like code below

db.Notes
  .Join(
       tickets,
       note => note.TicketID,
       tick => tick.ID,
       (note, tick) => new Mapper() { Note = note, Ticket = tick }
  );

gives the next error when I do .ToListAsync():

Unable to create a constant value of type Ticket. Only primitive types or enumeration types are supported in this context.

---

To write the full query I want to have like the first code block seems to be hard because I've a lot of options to query on. Using EF is that a lot easier.

Answer 1

Remove the assignment from your query and you are fine.

var list = db.Notes
     .Join(
         tickets,
         note => note.TicketID,
         tick => tick.ID,
         (note, tick) => new { note, tick}

     )
     .ToList();
     foreach(var item in list)
          item.note.Ticket = item.tick;

Alternativly you can also make a new Note() but than you have to copy it property by property.

If this is a Relation established in the EF-Model, you don't need the last iteration, and you do not need the entire Join.

It would be as short as

   db.Notes.Include(tickets)

with the same content loaded, but grouped by item, which is more often and more useful.