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!

asked Mar 13 at 18:07

JS1's gravatar image

JS1
511


2 Answers:

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.

answered Mar 14 at 12:44

Alexey%20Kulakov's gravatar image

Alexey Kulakov
52715

Here is a simple project to play around for you. Our Community license will be enough for experiments. The link will be actual for a while.

(Mar 14 at 12:49) Alexey Kulakov Alexey%20Kulakov's gravatar image

Hey Alexey... added a comment on top of yours but just realized it is posted as a response to my own question, so not sure if that alerts you or not. I am replying to your comment just in case. Thanks!

(Mar 14 at 13:46) JS1 JS1's gravatar image

Actually, one more question: unless I have missed something in your documentation, it appears as though you do not have an automatic code generation utility like SQLMetal which can be pointed at a database and automatically generate Entity objects for every table that already exists. Does a tool like that exist for SQL Server, or would we have to manually create each Entity and then map it to an existing table structure? Thanks!

(Mar 14 at 15:48) JS1 JS1's gravatar image

As far as I know Linq-to-Sql creates classes based on database structure. This method is also known as Database-First. That method makes you think in terms of tables, columns and so on. DataObjects.Net is Code-First ORM. Code-First method allows you not to thing about tables but be on OOP "field" and think in terms of classes and properties. So you develop classes and their relations and ORM creates corresponding tables , columns, foreign keys, indexes and so on based on these classes.

(Mar 15 at 10:41) Alexey Kulakov Alexey%20Kulakov's gravatar image

You don't have to do any map an Entity to a database table. DataObjects.Net analyzes structure of classes and build such map by itself. DataObjects.Net Domain is ready you can open a session and transaction and perform queries, create new Entities and update them. I will add some comments about what is going on to the example project I have posted in the first comment.

(Mar 15 at 10:50) Alexey Kulakov Alexey%20Kulakov's gravatar image

Thanks a lot for your response! Of course I understand that the code-first approach is what you guys will normally use, and that is what the project that you provided already does. However, what if we already have an existing database? Obviously we'd like some automatic Entity Class generation to be built based on our existing DB, much the same as SQLMetal.exe can do. Just so I am clear, is there such a tool in your solution? And if not, would you consider adding it? It is extremely useful, as not everyone can afford to start with a code-first approach when retrofitting existing projects.

(Mar 15 at 11:50) JS1 JS1's gravatar image

Unfortunately, we don't have code generation by existing database and we don't have near future plans for additing such tool. We have two legacy DomainUpgradeMode's - LegacySkip and LegacyValidate. You can use them on existing database, but you will have to implement Persistent types anyway. Also I can recommend you to use DomainUpgradeMode.Validate. You will get difference between model and database schema if any exists, it might be helpful

(Mar 15 at 12:56) Alexey Kulakov Alexey%20Kulakov's gravatar image

Thanks Alexey... that's what I thought. I would suggest that you should perhaps think about this, as it would be a great selling point for your code library. Many people have existing large database installations (for example, in large corporations) that they cannot just abandon and start from scratch using a code-first approach. I would think that you could really expand your customer base if you added this tool... in the meantime, I think you have answered all my questions. Much appreciated!

(Mar 15 at 13:43) JS1 JS1's gravatar image

Hey Alexey... not sure how the comments alerts work, so I am adding a comment here too. 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 at 13:05) JS1 JS1's gravatar image

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 at 13:41

JS1's gravatar image

JS1
511

"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 at 09:23) Alexey Kulakov Alexey%20Kulakov's gravatar image

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 at 09:51) Alexey Kulakov Alexey%20Kulakov's gravatar image

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 at 10:04) Alexey Kulakov Alexey%20Kulakov's gravatar image

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 at 10:15) Alexey Kulakov Alexey%20Kulakov's gravatar image

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 at 11:56) JS1 JS1's gravatar image

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 at 11:56) JS1 JS1's gravatar image

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 at 09:39) JS1 JS1's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA