While I have been using Entities for quite a while now, I have only recently gotten into Typed Views on a new project at work. As such, I have some relatively beginner questions. For all questions/scenarios, assume SQL Server 2000 with ASP.Net 2.0.

1) As far as performance goes, is it fair to say that, in general, performance for getting data in a read-only manner to the presentation (web) is fastest with TypedViews, then TypedLists and the finally Entities?

2) There is no way to have any type of hierarchy with Typed Views, correct?

3) I'm unclear on how Typed Views allow filtering and sorting. If db views don't allow parameters and I haven't included any sorting or criteria in the design of the view, how does LLBL get the sorting and filtering buckets and expressions into the query? Does the query execute the view and then filter/sort the results that are returned? Or does LLBL somehow override the SQL that is actually executed? If I had 1 millions orders in my database and only wanted to show the orders for customer X, would I just create a generic view in the DB and then add the appropriate filters before I called the view via DataAccessAdapter? Is that the most performant way to get that information?

4) With a view, is it possible to get the following (if so, how): Supposed each Product is related to one or more Category (Product -> ProductCategory <- Category). Is it possible to get a view that lists all of the Categories for each Product into one field? I'm looking for output like: ProductID | ProductTitle | ProductCategories 1000 | Hammer | 20,30,40 1001 | Screwdriver | 55,66,77 1002 | Saw | 10,11,12

Why? Because I need all of the Products but I also need all of the associated Categories. In the UI binding, it is trivial to drop the ProductCategories into an array and loop through each value and pull the description from an in-memory lookup table. I would think that getting the data back in that format is more efficient than: ProductID | ProductTitle | ProductCategory 1000 | Hammer | 20 1000 | Hammer | 30 1000 | Hammer | 40 1001 | Screwdriver | 55 1001 | Screwdriver | 66 1001 | Screwdriver | 77 etc.

In the past, I've just used entities (ProductEntity with a PrefetchPath to Category to get the description). However, while using the entity hierarchy is convenient, if it is much less efficient, then I would like something more efficient like Typed View.

5) If I can't get the data like the first example in #4 and I have to get the data back like the second example, does anyone have the block of (pseudo-)code to iterate through and changing the logic for each new Product? Yes, I know. Basic programming task but I always managed to screw it up in class the first couple of iterations by missing an end case. Not to mention that I haven't been in any type of class in 10+ years.

Thanks!

1) It all depends really. A view is usually in place to provide sql query that returns all relevant information. So if your view joins between 3 tables and you are only looking for records from one table then a typed list or dynamic list would be more efficient and flexible. Some views can be indexed, but there are strict limitations on this, just look up indexed views. Otherwise there is no difference in performance between a typed list and a typed view.

2) True, views do not have relationships. You can insert a sql view into your LLBLGen project as an entity and then create your own relationships, but these will be entities and not typed views.

3) They are treated just like a table, you can execute select queries against a view just as you would a table, including sorts and filters. The filtering and sorting is all done sql side if using predicate and sort expressions. Take a look at the TypedView section of the manual, it will be very familiar to how you have queried collections.

4) You can do something like this in a sql statement, but the performance usually won't be the best their either. You will want to perform some type of recursive query in your typed view that will just return as a some type of character type that is comma separated.

5) You could sort by id then iterate through and maintain the current id. If it's different then add the current productcategory to the summary field. If it's the same id add ", productcategory" to the summary field for that id.

bclubb wrote:

1) It all depends really. A view is usually in place to provide sql query that returns all relevant information. So if your view joins between 3 tables and you are only looking for records from one table then a typed list or dynamic list would be more efficient and flexible. Some views can be indexed, but there are strict limitations on this, just look up indexed views. Otherwise there is no difference in performance between a typed list and a typed view.

Of course, I knew the answer would be "It depends". I meant to phrase the question to imply that for all three scenarios the exact same data was being returned. I did learn two things from your answer though, so thanks.

bclubb wrote:

2) True, views do not have relationships. You can insert a sql view into your LLBLGen project as an entity and then create your own relationships, but these will be entities and not typed views.

Hmmm... Not quite following this one. Would this be different than what I've already done by adding my view to LLBL? Is there a difference between that and adding it so that it is an entity?

bclubb wrote:

3) They are treated just like a table, you can execute select queries against a view just as you would a table, including sorts and filters. The filtering and sorting is all done sql side if using predicate and sort expressions. Take a look at the TypedView section of the manual, it will be very familiar to how you have queried collections.

Ah, the lightbulb just turned on for me for views. I didn't realize that they were like tables that can be queried. I meant to mention that I had looked in the Help and still had questions. It's amazing that I've made it this long (10+ years) without having worked with views much (at all) up till now.

bclubb wrote:

4) You can do something like this in a sql statement, but the performance usually won't be the best their either. You will want to perform some type of recursive query in your typed view that will just return as a some type of character type that is comma separated.

Any thoughts on the ideal way to approach this? I was sure that it wouldn't be as performant as a regular query, but if I need the related data, is it easier and faster to do it via a query like I mentioned? Or just give in and use Entities and all the associated overhead?

bclubb wrote:

5) You could sort by id then iterate through and maintain the current id. If it's different then add the current productcategory to the summary field. If it's the same id add ", productcategory" to the summary field for that id.

Yeah, that's what I figured but was kind of hoping for a silver bullet.

Thanks for the response.

4) This is kind of what I was looking for: http://www.sqlteam.com/item.asp?ItemID=2368. Still not quite as easy as I was hoping for but definitely a starting point.

bclubb wrote: 2) True, views do not have relationships. You can insert a sql view into your LLBLGen project as an entity and then create your own relationships, but these will be entities and not typed views.

Hmmm... Not quite following this one. Would this be different than what I've already done by adding my view to LLBL? Is there a difference between that and adding it so that it is an entity?

You can't relate Typed Views to entities.

You can map a View to a TypedView and/or you can map it to an Entity. And thus you can work with it as if it is an entity so you can add relations between it and other entities, and if you want to retreive all the records inside the view, you will get them as an EntityCollection. The only difference between a View mapped to an Entity and a Table mapped to an Entity is that you can't insert in that view.

Walaa wrote:

You can map a View to a TypedView and/or you can map it to an Entity. And thus you can work with it as if it is an entity so you can add relations between it and other entities, and if you want to retreive all the records inside the view, you will get them as an EntityCollection. The only difference between a View mapped to an Entity and a Table mapped to an Entity is that you can't insert in that view.

Those are the pieces I was missing. Thanks for the follow-up!

Now, any anectdotes on whether I want to use Entities, some crazy query to get all my IDs on one row, or just join the tables in a View and use a TypedView and do the logic to group them myself?

Now, any anectdotes on whether I want to use Entities, some crazy query to get all my IDs on one row, or just join the tables in a View and use a TypedView and do the logic to group them myself?

The famous answer: "It depends"

I always use Entities when I need to use Entities, that's to say when I need to have relations defined with other Entities. And/Or if the entities are mapped on tables and I want the read/write mode.

If you want to use your data for read-only purposes and there is no need for relations defined with other entities then I go with one of the following options:

1- I tend to use Views and Typed Views, when the Join Query is rather complex, or when I want to have some computed columns along the way. And I don't have any need for relations, and if it's ok with me to take the responsibility of maintaining my View code defined on the database.

2- Else I might use a Typed List defined at design time, using tables already mapped to entities.