I am working on some code where I track the the changes to a Task in my system over time with an entity called HistoryItem. This is actually a new feature to an old system, so no HistoryItems exist yet. One item I would like to pull into my query is called a TechNote, this record of someone doing work for a client and I'd like to union those matching records with any other HistoryItem records that get created so I can show a complete history of both items.

Below is the code combining my query for matching HistoryItems and then I'm trying to convert matching Technotes into a similar results as HistoryItem so they can used in a union. I'm getting the error that it can't convert a DynamicQuery to an EntityQuery.

So my question is what is the best way to get this done? Is this doable but I just don't have the right query? I know I could make them both DynamicQuery's, but I would like to use the HistoryItem entity as my main object to get this data in my web app because really once I'm done, even the TechNote's will have their own history items I can pull in. So this is kind of temporary for now, but I would like to see if this is possible.

            var qf = new QueryFactory();
            var q = qf.HistoryItem
                .WithPath(HistoryItemEntity.PrefetchPathUser)

                .Where(HistoryItemFields.EntityId.Equal(Id))
                .Where(HistoryItemFields.EntityTypeId.Equal(EntityType.Task))

                .OrderBy(HistoryItemFields.CreatedAt.Descending());
            var q2 = qf.Create()
                    .Select(() => new HistoryItemEntity()
                    {
                        Id = 0,
                        EntityId = TechNoteFields.Id.ToValue<int>(),
                        EntityTypeId = EntityType.Technote,
                        UserId = TechNoteFields.UserId.ToValue<int>(),
                        ActionId = Utility.Enums.HistoryAction.Added,
                        CreatedAt = TechNoteFields.CreatedAt.ToValue<DateTime>(),
                        Description = "",
                        Data = ""
                    })
                    .From(qf.TechNote)
                    .Where(TechNoteFields.TaskId.Equal(Id));
            q.Union(q2);

A union query will result in one query that's been executed with the UNION or UNION ALL statement, so it will result in one resultset, and therefore the return type will be the one of the first query. The fluent API dictates the type of the query that's to be appended, and it's not possible to add a dynamic query as a unioned query to an entity query.

As you project one type to another, it's unclear to me how a historyitem can be a technote, you have some form of entity inheritance in place?

So in a union query, you can combine different tables of data as long as you are selecting the same number of columns with the same column types. That is what I'm basically trying to do.

I'm basically trying to have a technote item look like a history item so I can display it in a chronological list.

Please give it a try ... using an entity query for the second query.