Posts Tagged ‘LINQ’
Few days ago I was writing a class, that was simply wrapped for a collection of other classes (with same interface), aggregate class. The class also had few methods, where the logic was simple. Let’s say one method M. Other classes having same method as well. This method was simple transformation of data with same output as input. The aggregate class was simply calling M method of first, second, … of other classes.
I started with something like this:
function T M<T>(T data)
{
T tmp = data;
foreach (var c in classes)
{
tmp = c.M(tmp);
}
return tmp;
}
But then I had some weird wave in my brain and started thinking. I could create collection of functions, like IEnumerable<Func<T, T>> and call methods from this collection. Wait a minute… I can create from this collection of function one aggregate function and call just this one. Crazy? 
Probably. But it’s a nice way to keep my brain running.
It turned out, it’s pretty easy with LINQ:
public static Func<T, T> Compose<T>(this IEnumerable<Func<T, T>> source)
{
return source.Aggregate((agg, fn) => (d => fn(agg(d))));
}
I don’t think it’s any more (less) useful than the foreach with direct method calls, but it’s more succinct, more functional and more fun. 
I’m happy to bring you early Christmas gift packed as ADO.NET provider for Firebird version 2.7. This version brings important bug fixes (tracker.firebirdsql.org) and logging improvements.
This release wouldn’t be possible without support of people/companies using provider actively. Big thanks to them.
You can download it at www.firebirdsql.org or use NuGet package.
Enjoy!
When I’m teaching my Entity Framework trainings, I’m always begging to look, at least from time to time or when you see the query looks complex, to generated SQL statement. And if you have (near to) real data, also execution plan. Although Entity Framework helps you with standard data access layer, it’s not magic – the query translation is complex process and sometimes what you capture in LINQ query isn’t exactly how you’d express it in SQL. You simply have different concepts in LINQ vs. in SQL.
Last week I was writing some decision algorithms based on data and I was accessing it, of course, using Entity Framework. Because the conditions we’re complex I was writing these as it came from my head to my fingers. The day after I was writing similar condition, only one or two options negated and I wrote it differently. Basically I was swapping All and Any methods. These two are interchangeable, if you change conditions accordingly.
As an example let’s have and condition: “All apples are green.” aka “All(apple => apple.Color == Green)“. But you can also say “No (any) apple is non-green.” aka “!Any(apple => apple.Color != Green)“.
Now the magic comes to play. You might think, well, if it’s interchangeable, then it’s good, as Entity Framework can always utilize EXISTS predicate from SQL. For simple queries maybe. But if you think about various places where the condition can occur and how easy is to negate the condition you immediately have a lot of problems in front of you. Add to this database engine optimized, where it can or can’t use properly indices, reorder conditions to create smaller intermediate result sets etc. A lot of places where the machine needs to (try to) figure out what’s best way of getting your data for you.
Sadly there’s no rule of thumb, like always use Any. Only one good and 100% working advice is to always check the query and execution plan. But even with i.e. All the result could be absolutely fine.
I’m proud to announce new version of ADO.NET provider for Firebird – 2.6.5. It’s half maintenance release, half new features.
You can find all bug fixes in tracker.
The new features include and improvements:
* Support for Trace API in Firebird 2.5.
* Improvements in SQL generation for Entity Framework.
* Support for commands logging .
* Slightly faster command execution of big queries.
* And a lot of small code improvement making it more stable…
You can download it at http://sourceforge.net/projects/firebird/files/firebird-net-provider/2.6.5/ or http://www.firebirdsql.org/en/net-provider/.
Hope you’ll enjoy the release.
Firebird .NET provider team is proud to announce next version of ADO.NET provider for Firebird – 2.6.0.
This version contains various bug fixes as well as new features and improvements. And of course support for some of new Firebird server features. Highlights:
- .NET 4 Client Profile support
- Entity Framework 4 support
- Command cancellation support
- Support for UTF8 filenames
All changes can be found at tracker.firebirdsql.org.
You can download it at http://sourceforge.net/projects/firebird/files/firebird-net-provider/2.6.0/ or http://www.firebirdsql.org/index.php?op=files&id=netprovider.
Thanks to all who helped improving and hunting issues.
I almost forgot to announce the topics touching ADO.NET provider for Firebird. This year I have three sessions. You can find more info, and register, of course, at firebird-conference.com. Here’s the list.
- New features in .NET provider for Firebird
- Connecting to Firebird using .NET provider from various devices – iPad, iPhone, Android, Windows Phone,…
- Creating services for rich internet applications using Firebird and the OData protocol
So if you’re interested in any of these topic, come, ask, participate. Especially hearing scenarios in what you’re using the .NET provider is very valuable for me. I also like talking between sessions, I always learn something new and I’m eager to to my best to answer all questions people are asking.
I recently discovered nice feature of NHibernate. It’s called MultiQuery (but the name doesn’t matter). The idea behind is simple. Instead of sending multiple queries one by one and melting performance of your application in network latency, send all in one batch.
I read couple of articles about it. Later something in my head started to working and I had an idea about trying to do it in Entity Framework. 
I had a basic concept in my head in couple of minutes and I told myself I’ll try to do it, but I’ll not invest too much time into it. Just quick’n'dirty brain exercise for Saturday (alike Bart de Smet‘s Crazy Sundays).
The concept was simple. Record couple of ObjectQuery objects, get commands out of these, create one huge batch, re-wire parameters (more about that later) and get results.
public class MultiQuery
{
struct QueryRecord
{
public ObjectQuery Query { get; set; }
public Type Type { get; set; }
public static QueryRecord Create<T>(ObjectQuery<T> query)
{
return new QueryRecord() { Query = query, Type = typeof(T) };
}
}
#region Fields
ObjectContext _context;
List<QueryRecord> _queries;
#endregion
#region Constructors
public MultiQuery(ObjectContext context)
{
_queries = new List<QueryRecord>();
_context = context;
}
#endregion
#region Public Methods
public MultiQuery Add<T>(ObjectQuery<T> query)
{
if (query == null)
throw new ArgumentNullException("query");
_queries.Add(QueryRecord.Create(query));
return this;
}
public MultiQuery Add<T>(IQueryable<T> query)
{
return this.Add(query as ObjectQuery<T>);
}
public IEnumerable<ObjectResult> Execute()
{
IDbConnection storeConnection = ((EntityConnection)_context.Connection).StoreConnection;
using (IDbCommand cmd = storeConnection.CreateCommand())
{
IDataParameterCollection parameters = cmd.Parameters;
cmd.CommandText = CreateCommand(_queries.Select(q => q.Query), cmd.CreateParameter, ref parameters);
bool shouldClose = (_context.Connection.State == ConnectionState.Closed);
try
{
storeConnection.Open();
using (IDataReader reader = cmd.ExecuteReader())
{
int cnt = 0;
do
{
yield return _context.Translate(_queries[cnt].Type, reader);
cnt++;
} while (reader.NextResult());
}
}
finally
{
if (shouldClose)
storeConnection.Close();
}
}
}
#endregion
#region Private Methods
string CreateCommand(IEnumerable<ObjectQuery> queries, Func<IDataParameter> parameterCreator, ref IDataParameterCollection parameters)
{
List<string> commands = new List<string>();
int cnt = 0;
foreach (var q in _queries.Select(q => q.Query))
{
string query = q.ToTraceString();
foreach (var p in q.Parameters)
{
IDataParameter parameter = parameterCreator();
parameter.ParameterName = string.Format("@p{0}", cnt++);
parameter.Value = p.Value;
parameters.Add(parameter);
// Not good. Better (and still easy) idea?
query = query.Replace(string.Format("@{0}", p.Name), parameter.ParameterName);
}
commands.Add(query);
}
return string.Join(";" + Environment.NewLine, commands);
}
#endregion
}
static class MultiQueryExt
{
internal static ObjectResult Translate(this ObjectContext context, Type type, IDataReader reader)
{
// ObjectResult<TElement> Translate<TElement>(DbDataReader reader)
object result =
context
.GetType()
.GetMethod("Translate", new[] { typeof(DbDataReader) })
.MakeGenericMethod(type)
.Invoke(context, new object[] { reader });
return (ObjectResult)result;
}
}
I’m here fully utilizing new Translate method in Entity Framework 4 (for v1 similar method is available in EFExtensions). The rest is done using pure ADO.NET. It’s worth noting, that this code, same as in NHibernate, works only if the database and the underlying provider supports processing more queries in one command (i.e. Microsoft SQL Server does, but Firebird does not).
Also small notice to parameters. I’m doing simple replace and that’s dumb. It may fail and produce wrong results, but in very rare cases. So you should test thoroughly. The case when it produces wrong results is, when you write query in where you use directly (not as a variable etc.) string that is same as parameter name (i.e. p__linq__<number> for SqlClient or p<number> for FirebirdClient). As you are in control of these strings you can change the code to use a variable, for instance.
A lot of “fetching” methods in Entity Framework supports also MergeOption. Adding overload for Execute I’m leaving as exercise for readers. Likewise for the Entity SQL queries.
If you’re composing queries in LINQ on various places, utilizing the delayed execution, you might be surprised, that some of your dynamically added OrderBys are not in final query.
Imagine we have a simple table with a and b columns (and primary key). If you write following query with ordering adding (it can be in different method etc.) …
IQueryable<OrderingTest> tmp1 = context.OrderingTest;
tmp1 = tmp1.OrderBy(x => x.a);
tmp1 = tmp1.OrderBy(x => x.b);
//Console.WriteLine((tmp1 as ObjectQuery).ToTraceString());
… the result will contain sorting based only on b column. That’s because the last OrderBy took the precedence.
To make it work as expected you have to write it like this.
IQueryable<OrderingTest> tmp2 = context.OrderingTest;
tmp2 = tmp2.OrderBy(x => x.a);
if (tmp2 is IOrderedQueryable<OrderingTest>)
tmp2 = (tmp2 as IOrderedQueryable<OrderingTest>).ThenBy(x => x.b);
//Console.WriteLine((tmp2 as ObjectQuery).ToTraceString());
The secondary (and further ordering) needs to be done via ThenBy method, which is available on IOrderedQueryable<T>. That’s the reason for casting.
Sure it’s always safer to do this in one place directly, but sometimes the query is and has to be build on various places. Then think if a call to OrderBy could or couldn’t be at some place before.
You can always do some “dummy sort”, like _ => 0, initially and then use only ThenBy, but I personally don’t like playing with the intelligence of optimizer. It may have bad impact on performance.
Previous two functions (function 1, function 2) I presented were doing something that wasn’t core part of LINQ and it was up to you to create it. On the other hand, this function is different. It’s just a helper to simplify writing of left outer join (or right outer join, depending on what collection you consider to be on left/right side). Not because it’s hard to write it, but because it involves couple of lines and repeating it all the time is just boring.
internal static IEnumerable<TResult> LeftOuterJoin<TOuter, TLeft, TKey, TResult>(this IEnumerable<TOuter> outer, IEnumerable<TLeft> left, Func<TOuter, TKey> outerKeySelector, Func<TLeft, TKey> leftKeySelector, Func<TOuter, TLeft, TResult> resultSelector)
{
return
from o in outer
join r in left on outerKeySelector(o) equals leftKeySelector(r) into j
from r in j.DefaultIfEmpty()
select resultSelector(o, r);
}
Nothing tricky. You can find this in many examples, I just wrapped it into method and parametrized it a little. Enjoy.
Similarly to previous method for Interleaving two IEnumerables I needed one method for doing kind of aggregate, but also to know about the intermediate steps. I could abuse the Aggregate method either directly or indirectly but I’m always more happy with clean solution.
The method is pretty simple (but before I did some rework, it was ugly ). It takes, apart from the IEnumerable, two functions. One for setting up the initial value for the first item and one for getting result for next step.
internal static IEnumerable<TResult> IncrementalAggregate<TSource, TResult>(this IEnumerable<TSource> data,
Func<TSource, TResult> init,
Func<TSource, TResult, TResult> nextResult)
{
bool first = true;
TResult intermediate = default(TResult);
foreach (var item in data)
{
if (first)
{
intermediate = init(item);
first = false;
}
else
{
intermediate = nextResult(item, intermediate);
}
yield return intermediate;
}
}
With it you can do i.e. summing the numbers and know what the intermediate sums were. Yes, sounds weird, but you might need it, one day as I did.
I was recently doing some work related to custom reporting and I needed to simply interleave two streams of data (it was actually same source, but different items selected). Kind of select first item from the first stream, first from the other, second from first, second from other etc. No big deal.
To make this easily doable I created simple extension method for me.
internal static IEnumerable<T> Interleave<T>(this IEnumerable<T> first, IEnumerable<T> second)
{
using (IEnumerator<T>
enumerator1 = first.GetEnumerator(),
enumerator2 = second.GetEnumerator())
{
while (enumerator1.MoveNext())
{
yield return enumerator1.Current;
if (enumerator2.MoveNext())
yield return enumerator2.Current;
}
}
}
It keeps reading elements from the first stream and if there’s enough in the second stream then interleave. If the second one isn’t “long” enough, it’ll keep returning only items from first one. If the second one is “longer”, it’ll stop when the first one is empty. If you need handle these cases differently, you can either change the method or preprocess the streams before using this method.
It may came as a shock but Firebird does not have direct support for neither bools nor guids. On the other hand people around Firebird are smart and came with more or less standard solutions for both. The bool is easy, just use number with constraint to 0 or 1. For guid we (ab)use special character set available in engine. It’s called OCTETS and it’s exactly what you think it is. Just a bunch of binary data, without any other interpretation from engine. That means CHAR(16) and the above character set is a perfect match for storing (not only) guids.
On the other side, where .NET Framework and Entity Framework lives, the bool and guid datatypes are core part of both frameworks. This created a small mismatch or better to say inconvenience. It wasn’t showstopper but working with (or actually without) it wasn’t pleasure either.
But this is over, since today, I used similar trick we used for “identity” columns and added two new special keyword (if I can call it like that). #BOOL# and #GUID#. When you use these (we’re looking for these in whole comment, so you can place it anywhere you want), your model will contain properties with accordant types. (Note, we’re not doing any checks whether your underlying datatype is compatible, it’s up to you.) Similarly the internals of Entity Framework support were improved to handle these changes correctly (as well as Model First support).
If you wanna try it, grab it from SVN or weekly builds and enjoy. And report any problems you encounter, of course.
Few days ago I posted an extension method to run projection on a collection in parallel. The method has one problem. It’s not dealing with exceptions. And because the ordering wasn’t (and isn’t) implicitly preserved, I did this small improvement.
Right now the method returns simple structure with original item, the result (if no exception occured, sure) and exception (if any). I didn’t went to AggregateException (although you can modify the code yourself to use it). Now you can decide while consuming what to do when exception occurred. Adding some kind of cancellation shouldn’t be difficult.
The idea behind is the same as in previous version.
#region ParallelProjection
internal struct ParallelProjectionResult<TSource, TResult>
{
public TSource Item { get; set; }
public TResult Result { get; set; }
public Exception Exception { get; set; }
}
internal static IEnumerable<ParallelProjectionResult<TSource, TResult>> ParallelProjection<TSource, TResult>(this IEnumerable<TSource> source, Func<TSource, TResult> projection, int maxParallelism)
{
BlockingCollection<ParallelProjectionResult<TSource, TResult>> results = new BlockingCollection<ParallelProjectionResult<TSource, TResult>>();
ThreadPool.QueueUserWorkItem((o) =>
{
Semaphore semaphore = new Semaphore(maxParallelism, maxParallelism);
CountdownEvent countdown = new CountdownEvent(1);
try
{
foreach (var item in source)
{
countdown.AddCount();
semaphore.WaitOne();
ThreadPool.QueueUserWorkItem(
(element) =>
{
TSource e = (TSource)element;
ParallelProjectionResult<TSource, TResult> result = new ParallelProjectionResult<TSource, TResult>();
result.Item = e;
try
{
result.Result = projection(e);
}
catch (Exception ex)
{
result.Exception = ex;
}
results.Add(result);
semaphore.Release();
countdown.Signal();
},
item);
}
countdown.Signal();
countdown.Wait();
results.CompleteAdding();
}
finally
{
if (countdown != null)
countdown.Dispose();
if (semaphore != null)
semaphore.Dispose();
}
}, null);
return results.GetConsumingEnumerable();
}
#endregion
Here’s updated version of the method.
I have here another not-general-purpose-parallel/multihtreaded-method. To make a long story short I needed do some transformation on collection’s elements, aka projection. Unfortunately the method I was plugging in was doing some network requests, in fact couple of requests. Sequentially, blocking processing until the response came back. I know a proper way will be to turn these requests into asynchronous, unluckily this was part of bigger architecture I could not change. I didn’t want to use AsParallel method as I expected a need for more control maybe sometime later. So I solved it abusing ThreadPool threads. Bad for scheduler and memory, as I’ll be wasting threads and resources, blocking, until reply is sent by server, but very easy for me. I told you, abusing.
So I came with this method. It’s utilizing new .NET Framework 4 concurrent collections, BlockingCollection in particular as it’s great for producer-consumer scenario and I want the method to return results whenever one is done (that also implies the ordering isn’t preserved).
internal static IEnumerable<TResult> ParallelProjection<TSource, TResult>(this IEnumerable<TSource> source, Func<TSource, TResult> projection, int maxParallelism)
{
BlockingCollection<TResult> results = new BlockingCollection<TResult>();
ThreadPool.QueueUserWorkItem((o) =>
{
Semaphore semaphore = new Semaphore(maxParallelism, maxParallelism);
CountdownEvent countdown = new CountdownEvent(1);
try
{
foreach (var item in source)
{
countdown.AddCount();
semaphore.WaitOne();
ThreadPool.QueueUserWorkItem(
(element) =>
{
results.Add(projection((TSource)element));
semaphore.Release();
countdown.Signal();
},
item);
}
countdown.Signal();
countdown.Wait();
results.CompleteAdding();
}
finally
{
if (countdown != null)
countdown.Dispose();
if (semaphore != null)
semaphore.Dispose();
}
}, null);
return results.GetConsumingEnumerable();
}
The method is straightforward, a lot of work was saved using the smart blocking collection. I’m simply reading items from the collection and applying the function to them. To not overload the system with huge number of threads I also added maxParallelism parameter. When this number of threads is processing items, I’ll stop scheduling more, using Semaphore, until some are done and again available. When there’s no item in source collection available and all item were processed I call CompleteAdding method to say I’m done and there will be no other items. Here I’m using CountdownEvent class initialized to 1 as you can’t, of course, add items if it reaches 0. Before final Wait I’m subtracting one to compensate this.
And that’s it. Again, it’s not general purpose method. Use with care, it may bring you even worse performance if wrongly used.
Scott Guthrie recently posted article about New Embedded Database Support with ASP.NET. This made me think about other options, Firebird in particular, and advantages and disadvantages. What I’m going to do is to very shortly introduce Firebird Embedded here and then compare it with features Scott wrote in his article.
Firebird Embedded, shortly, is Firebird database server in one DLL. No need to install etc., just load this DLL and use it. To be precise, there are some other DLLs, i.e. to support national charsets, but it’s still in under 10MB all. The database itself is built from same sources as “full” server and it’s not limited in any way.
Works with Existing Data APIs – as I said, Firebird Embedded is based on same codebase as “full” server, thus the SQL and API is same. And not only this, the ADO.NET provider for Firebird works with it and you’re programming using same thinking.
No Database Installation Required and Database Files are Stored on Disk – databases created by Firebird Embedded are stored wherever you want, with any extension. Firebird itself doesn’t have any master database, thus even the “full” server works with any (valid database) file.
Shared Web Hosting Scenarios Are Now Supported with SQL CE 4 – sure you don’t need to install anything with Firebird Embedded, that’s why it’s called Embedded. And not only this, from version 2.5, you can open database from different processes, i.e. IIS worker process and Apache workers or some console application doing something in background.
Visual Studio 2010 and Visual Web Developer 2010 Express Support – as the tool set for Firebird Embedded is exactly same as for “full” Firebird, you can use DDEX (aka Server Explorer support), Entity Framework (LINQ), …
Supports Both Development and Production – this is something I’m silently expecting. But yes, you can do the same with Firebird Embedded too.
Easy Migration to SQL Server – ahh, my favorite point. The Firebird Project has of course whopping number of tools to support migration from Embedded to “full”. The most used is … nothing. The databases are fully compatible and you can switch servers without any other tools, migration, conversion, … Just place it where you want it and connect to it, either with “full” or Embedded Firebird. And to switch your application? Again nothing. Same ADO.NET provider, just change connection string, if needed. Mostly you’ll add server IP address and maybe different path and manually switch server type, if you want. Really the migration is so simple. It is one minute task and thanks to same tool set, no matter what you version your targeting during development, your application will work with the other as well without any additional effort.
You like Firebird Embedded? I do, a lot. SQL Server Compact Edition 4 looks promising, but Firebird has something to offer as well. And recall, it’s based on same sources as “full” Firebird, very mature codebase, examined with tons of installations.
Yesterday I needed to put one element at beginning of the collection I already had. Some kind of Concat upside down. As you can use the Concat method, it looks weird when you see the code, because two items are actually swapped. So I created a simple extension method to do it for me.
I started with:
public static IEnumerable<T> Prepend<T>(this IEnumerable<T> source, T item)
{
yield return item;
foreach (var x in source)
yield return x;
}
It’s classic imperative approach, you’re expressing how you’ll do it. Then I thought: “Hey, why not to use LINQ methods already available.”. As you guess, I abused Concat method as I wrote above:
public static IEnumerable<T> Prepend<T>(this IEnumerable<T> source, T item)
{
return new[] { item }.Concat(source);
}
You can easily extend both methods to accept also collection as a second parameter.
If you’re more about declarative programming you’ll probably like the other one. But choose whatever fits your brain better.
The ADO.NET provider for Firebird now fully supports all the new features in Entity Framework 4. Simple. If you’re eager to test it, grab the weekly build and enjoy.
We support the internal improvements as well as the visible changes like i.e. Model First or CreateDatabaseScript method. The template for creating SQL script is now part of sources and sure will be included in final package as well. I expect to improve it on your feedback and also the model generation from designer is pluggable so you can create custom one and use it there.
To support some new features in SQL generation I had to tweak it little bit and as with every change, there’s a change that something goes wrong. Thus I would be more than happy to get some feedback either that it works OK or any queries where it fails.
I’m so happy to cross this milestone about two months after final Visual Studio 2010, .NET Framework 4 (incl. Entity Framework 4) were released. You can expect the official release after some testing, it’s your turn .
Though Entity Framework 4 doesn’t support bulk action, you can do it. As Matthieu Mezil shows, it’s possible. Nice piece of code, Matthieu.
If you don’t wanna play with it and go straight, you can create a stored procedure for action you need to call it. That’s how I’m doing it. Maybe in next version of EF somebody will think about bulk actions and we’ll see it.
And the v3/v4 is pretty awesome. It’s really going through a lot of options and dealing with it. Worth at least looking at it and quickly thinking about it.
As it may look like nothing is going on, it’s not true. Next to Firebird 2.5 new protocol features, I’m also working on Entity Framework v4 support. Before I go further, be sure, that all providers written for Entity Framework v1 are also working with v4.
In fact right now all the major improvements in Entity Framework v4 are supported. You can benefit from features available, like the LIKE translation support or plenty of new functions. My personal favorite is TruncateTime (so I can get rid of workaround). The Model First approach is next in a row. At least basic T4 template for start is my aim. The rest could be done by you, simply modifying the template. And also wiring the template into code so you can use it programmatically too. Under the cover, while working on new stuff I’m also finding ways to optimize the code. Luckily the changes will be noticeable.
The DDEX for Firebird supports Visual Studio 2010 and the full Entity Framework v4 support will be here soon – now you can try a weekly build. Feel free to ask about anything related.
From time to time I have to run two or more queries that I know will always be two or more – like some first/skip records and also total count. If you write it as two queries and execute, that means two round trips to database. Although it may not matter if the network latency is very small, why not to challenge myself and try to find some workarounds.
Sure you can create some stored procedures and get the data back from these, but I was thinking about more LINQ to Entitiesish way. I recalled a way I one time used inside one project. Although it was done in pure SQL, it, as it turned out, works, kind of, for LINQ to Entities as well.
The idea is using “one row table” and put the queries as columns. Let me demonstrate:
select
(select foo, bar from table1 where ...),
(select baz, foo from table2 where ...)
from OneRowTable;
Where the OneRowTable can be specially created table or i.e. for Firebird RDB$DATABASE or for Oracle Database dual. It isn’t the nicest SQL (and also challenges optimizer), but works. In columns as queries you can put anything you want as long as it is syntactically correct.
OK, what about the Entity Framework or LINQ to Entities respectively. I created the “one row table” first:
create table OneRowTable(x bit primary key);
insert into OneRowTable values (0);
The table needs to have the primary key to be able to import it into entity model, the datatype doesn’t matter (I was using MS SQL, hence the bit).
What about the queries? Similar approach:
var allinone = context.OneRowTable.Select(_ => new
{
AData = context.a.Where(a => a.x.HasValue && a.x.Value > 10).Select(a => new { A1 = a.id, A2 = a.id * 2 }),
BData = context.b.Where(b => b.id < 999).Select(b => new { B1 = b.id, B2 = b.y }),
});
string query = (allinone as ObjectQuery).ToTraceString();
var data = allinone.First();
var adata = data.AData;
var bdata = data.BData;
The a and b are my testing tables. You can check there’s only one query executed. Encapsulating this into some method is only piece of cake.
And how the query looks like? Well for my MS SQL test:
SELECT
[UnionAll1].[x] AS [C1],
[UnionAll1].[C2] AS [C2],
[UnionAll1].[C1] AS [C3],
[UnionAll1].[id] AS [C4],
[UnionAll1].[id1] AS [C5],
[UnionAll1].[C3] AS [C6],
[UnionAll1].[C4] AS [C7],
[UnionAll1].[C5] AS [C8],
[UnionAll1].[C6] AS [C9]
FROM (SELECT
[Project1].[C2] AS [C1],
[Extent1].[x] AS [x],
1 AS [C2],
[Project1].[id] AS [id],
[Project1].[id] AS [id1],
[Project1].[C1] AS [C3],
CAST(NULL AS int) AS [C4],
CAST(NULL AS int) AS [C5],
CAST(NULL AS varchar(1)) AS [C6]
FROM [dbo].[OneRowTable] AS [Extent1]
LEFT OUTER JOIN (SELECT
[Extent2].[id] AS [id],
[Extent2].[id] * 2 AS [C1],
1 AS [C2]
FROM [dbo].[a] AS [Extent2]
WHERE ([Extent2].[x] IS NOT NULL) AND ([Extent2].[x] > 10) ) AS [Project1] ON 1 = 1
UNION ALL
SELECT
2 AS [C1],
[Extent3].[x] AS [x],
[Extent4].[id] AS [id],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS int) AS [C4],
[Extent4].[id] AS [id1],
[Extent4].[id] AS [id2],
[Extent4].[y] AS [y]
FROM [dbo].[OneRowTable] AS [Extent3]
CROSS JOIN [dbo].[b] AS [Extent4]
WHERE [Extent4].[id] < 999) AS [UnionAll1]
ORDER BY [UnionAll1].[x] ASC, [UnionAll1].[C1] ASC
Not exactly the original shape. The translator took another way creating two one row results and using union all to get it into one query. Except this, the query is in general the same (the explicit joins are as result same as the subselects, though little bit more confusing in this case).
Again, this isn’t general purpose way of doing it and may result in worse performance than running queries separately and I would recommend using it only after careful testing and on controlled limited set of queries.
