Question

Imagine the following simple LINQ query implemented using LINQ to SQL:

            var q = from c in _DCO.Cities
                    select new Domain.Base.City()
                    {
                        ID = c.CityID,
                        Name = c.Name,
                        DistinctName = c.DistinctName,
                        DisplayName = c.DisplayName,
                        State = new Domain.Base.State()
                        {
                            Abbreviation = c.State.Abbreviation,
                            ID = c.State.StateID,
                            Name = c.State.Name,
                            UTCOffset = c.State.UTCOffset,
                            Country = c.State.CountryID.HasValue ?
                                new Domain.Base.Country(){
                                    ID = c.State.CountryID.Value,
                                    Name = c.State.Country.Name,
                                    ThreeLetterAbbreviation = c.State.Country.ThreeLetterAbbreviation,
                                    TwoLetterAbbreviation = c.State.Country.TwoLetterAbbreviation
                                } : null
                        }
                    };
            return q.AsQueryable();

Domain.Base.City, Domain.Base.State, and Domain.Base.Country are C# objects defined as domain objects in our code.

We translate the values from the LINQ DataContext tables and assign them to these objects as part of the query. After the query is executed, we have an IQuerable interface to a list of Domain.Base.City objects, with the State and Country sub-objects also filled. (_DCO is our LINQ DataContext object generated with SQLMetal).

What we want to do is be able to separate out the code which assigns values from the _DCO.States and _DCO.Countries database tables out into methods which can be re-used in the C# code. So for example:

var q = from c in _DCO.Cities select new Domain.Base.City() { ID = c.CityID, Name = c.Name, DistinctName = c.DistinctName, DisplayName = c.DisplayName, State = PopulateStateObject(c.State) }; return q.AsQueryable();

Inside PopulateStateObject() we would also have a similar call to PopulateCountryObject() which would fill all the fields. That would allow us to write one piece of code which assigned all the database table fields to the C# domain object and re-use that piece of code in multiple queries if we wanted to.

The problem with LINQ-to-SQL is that it can't do this without a significant performance hit, because for some reason it can't execute it as a single SQL query. If we populate the domain objects as shown in the original call it's much faster, but moving the code into a re-usable method makes it too slow, even if that method returns IQueryable.

Can your library be configured to handle a situation like this with future queries, prefetch, or something else? We'd like to have these sub-queries and pieces of LINQ-style code defined in methods which can be re-used. Thanks!

Answer 1

Hello JS1,

I transformed your initial query to DataObjects.Net version and got following query

var dtos = session.Query.All&ltCity>()
          .Select(
            c => new CityDto() {
              Id = c.Id,
              Name = c.Name,
              DisplayName = c.DisplayName,
              DistinctName = c.DisplayName,
              State = new StateDto() {
                Id = c.State.Id,
                Name = c.State.Name,
                Abbreviation = c.State.Abbreviation,
                UTCOffset = c.State.UTCOffset,
                Country = (c.State.Country!=null)
                  ? new CountryDto() {
                    Id = c.State.Country.Id,
                    Name = c.State.Country.Name,
                    ThreeLetterAbbreviation = c.State.Country.ThreeLetterAbbreviation,
                    TwoLetterAbreviation = c.State.Country.TwoLetternAbbreviation,
                  }
                  : null
              }
            }).ToList();

This query executes as one single SQL query. But I have noticed that you return q.AsQueryable(). Does that query suppose to be base for following query construction? If so, than it might be imposible to continue IQueryable building in some cases.

Also you waned to get rid of need to write such complicated objects initialization every time. It is clear to see that your Domain.Base.City and other classes are kind of Data transfer objects (or DTOs). Reasonably, you want to move Entity-to-DTO transformations into separate methods. I decided to put these methods into entities and name them .ToDTO(). For instance,

  [HierarchyRoot]
  public class City : Entity
  {
    [Field, Key]
    public int Id { get; private set; }
    [Field]
    public string Name { get; set; }
    [Field]
    public string DistinctName { get; set; }
    [Field]
    public string DisplayName { get; set; }
    [Field]
    public State State { get; set; }
public CityDto ToDto()
{
  return new CityDto() {
    Id = this.Id,
    Name = this.Name,
    DisplayName = this.DisplayName,
    DistinctName = this.DisplayName,
    State = this.State.ToDto()
  };
}

}
public class CityDto
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public string DistinctName { get; set; }
    public string DisplayName { get; set; }
    public StateDto State { get; set; }
  }

If we do the same thing with State and Country classes than we will be able rewrite our previous query into this

var dtos = session.Query.All&ltCity>().Select(c => c.ToDto()).ToList();

But there is one restiction - you cannot continue building IQueryable in most cases. You can continue using Linq for filtering and other things but you will have to use q.AsEnumerable()

var dtos = session.Query.All<City>()
  .Select(c => c.ToDto())
  .AsEnumerable()
  .Where(d=>d.Name.Contains("New")).ToList();

So if whan I have done is what you need than yes DataObjects.Net can do what you want. If not, than explain me where I'm wrong and I will try to help you.

Answer 2

0

Hey Alexey... thanks for the response! Regarding your solution above, you mentioned this piece of code:

var dtos = session.Query.All<city>() .Select(c => c.ToDto()) .AsEnumerable() .Where(d=>d.Name.Contains("New")).ToList();

Correct me if I am wrong, but when I transform this to AsEnumerable, wouldn't that cause the query to actually execute against the database at that point? In which case, it would pull out the entire Cities table before filtering it for New" in the name, correct? Obviously if that was the case we couldn't use it for performance reasons.

So my question is if there is a way to use the code you wrote above, include filtering in a WHERE clause, where the entire thing is still executed as a single query. If it can't be done based on what you wrote above, then is there some way to do it more intelligently with other features that are available in your code, such as future queries, or prefetch, so something else that i have missed? Thanks!

answered Mar 14 '17 at 13:41

JS1
5●1●1●1

"Correct me if I am wrong, but when I transform this to AsEnumerable..."

You're right. But I wanted to underline the fact you still can perform some operations after DTOs have created but you have to do it locally. But nothing stops you from applying a filter before you execute .Select(c=> c.ToDTO()) For instance,

var dtos = session.Query.All&ltCity>()
.Where(c=>c.Name.Contains("New") || c.Name.Contains("Buffalo"))
.Select(c => c.ToDto()).AsEnumerable() .Where(d=>d.Name.Contains("New")).ToList();

(Mar 15 '17 at 09:23) Alexey Kulakov

Future queries is a mechanism which allows you to execute several queries as one SQL batch. My example will have no sense in real life. Let's say you need to get all cities started with 'A', 'B' and 'C' and you need them in different collections. You can do this:

var a = session.Query.DelayedQuery(q=>q.All;ltCity>().Where(c=>c.Name.StartsWith("A")));
var b = session.Query.DelayedQuery(q=>q.All;ltCity>().Where(c => c.Name.StartsWith("B")));
var c = session.Query.All;ltCity>().Where(c =>c.Name.StartsWith("C"));

You'll get 3 queries batch. Try it

(Mar 15 '17 at 09:51) Alexey Kulakov

Prefetch is another useful API. But before telling you about Prefetch API I have to explain lazy loading. We support lazy loading of certain fields. For example, all Entity references and EntitySet content load lazily. If you have an Article with fields Title, Summary (will be shown in Articles list), and Text which is huge. Obviously you will want to load Text on demand. You can mark it as LazyLoad field. Once you need it DO will load it from database.

(Mar 15 '17 at 10:04) Alexey Kulakov

So Prefetch API is a mechanism which allows you to force DO load certain LazyLoad field. For example we have our Article and we want to get them from database. In that case DO will request Title and Summary only. But in certain case we might want to get full entity. We can do this

session.Query.All&ltArticle>().Prefetch(a=>a.Text)

This force DO to request all three fields instead of two.

(Mar 15 '17 at 10:15) Alexey Kulakov

OK. I think I understand both of these mechanisms now (Prefetch and Future Queries). So based on what you said about filtering before the IQueryable is transformed to IEnumerable, is it fair to say that as long as I build something in code, and as long as it all evaluates to IQueryable, even if the IQueryable is being built in pieces as part of separate methods, then all of that will get transformed into a resonably efficient query at the point where I call .ToDto()?

(Mar 15 '17 at 11:56) JS1

In other words, using the specific example you coded up, it should be possible to use building blocks for query pieces and have it all run efficiently? As long as what you provided with these .ToDTO() calls will run as efficiently as the original example where the object is instantiated directly in the query, then that solves our problem. Thanks again!

(Mar 15 '17 at 11:56) JS1

Hey Alexey... so, just to get a final answer on this: Is it fair to say that if I build a complex query with your system, using IQueryable for all the pieces, calling .ToDto() for internal parts of the query as you have shown, that when I call the final ToDto() at the end, that is where the query will actuallly get executed? And that it will have the same performance as if I had built all the Dto objects directly into the query instead of calling .ToDto() a number of times interally to build the sub-objects? Thanks!

(Mar 16 '17 at 09:39) JS1

Subscription:

Related questions