I guess, this question was already asked somewhere, but I couldn't find a thread that would give me an answer to my problem.

When fetching data from a db table, I can use prefetch paths to retrieve data from other related tables.

When I am fetching data from a db view, how can I also prefetch data from associated db tables. I understand, the prefetch path concept only works with table <-> relations. I also understand that there are no strict relations between a view and the tables, but the implicit information is there.

I'm looking forward to some hints.

You can add entities based on views:

• In the Project Explorer, right-click 'Entities' and choose 'Add New Entities Mapped On Views From Catalog(s)' instead (or press Ctrl + V).

• A screen will pop up, showing all database all views in the catalog. As you can map as many entities onto a single target as you'd like, all views are always listed. LLBLGen Pro automatically suggests names for entities for these targets, so if you already have an entity mapped on a given view, it will make sure the name suggested isn't clashing with the names already available in the project.

• When you're done checking entity checkboxes, click 'Add to project'.

• Then your DB-View now is an entity. You can now add custom relations between entities (including your entity mapped based on view).

daelmo wrote:

• Then your DB-View now is an entity. You can now add custom relations between entities (including your entity mapped based on view).

I had everything except the last step. Thanks for the hint. I think, I get the full picture now.

daelmo wrote:

You can add entities based on views:

• In the Project Explorer, right-click 'Entities' and choose 'Add New Entities Mapped On Views From Catalog(s)' instead (or press Ctrl + V).

• A screen will pop up, showing all database all views in the catalog. As you can map as many entities onto a single target as you'd like, all views are always listed. LLBLGen Pro automatically suggests names for entities for these targets, so if you already have an entity mapped on a given view, it will make sure the name suggested isn't clashing with the names already available in the project.

• When you're done checking entity checkboxes, click 'Add to project'.

• Then your DB-View now is an entity. You can now add custom relations between entities (including your entity mapped based on view).

After looking a bit closer on your suggestion, I have a question: If you can map any view into a table/entity, what is the reason/advantage to use views at all?

I can do anything with a table that I can do with a view, plus a number of additional things like setting customized relations. So why wouldn't I just convert every view into a table a drop/not use the views itself?

And another question: I know that the relation between a view and a table, is a 1:1 relation. Is there a way, to define the relation in LGP as a 1:1 and not m:1?

magic wrote:

After looking a bit closer on your suggestion, I have a question: If you can map any view into a table/entity, what is the reason/advantage to use views at all?

I can do anything with a table that I can do with a view, plus a number of additional things like setting customized relations. So why wouldn't I just convert every view into a table a drop/not use the views itself?

I can think in a few differences (could be interpreted as advantages or disaventages depending the scenario):

• Entities usually have a primary key.
• Above permits you create relations between other entities. There are situations when you don't want this: for example the view "CustomersWhoBuyCandiesLasMonth" that have all the necessary fields doesn't need to be related with any other entity. Or the view "ResumeOfSalesOf2008Year" maybe doesn't have PK neither possible relations with any entity.
• Views are read-only. You ussually want this.
• Views are a direct way to obtain specific data. For example, in reporting scenarios, some people use them to obtain info directly from DB and bound them to report viewers.

magic wrote:

And another question: I know that the relation between a view and a table, is a 1:1 relation. Is there a way, to define the relation in LGP as a 1:1 and not m:1?

The possible relations between entities depends upon the kind of primary keys they have and how you relate those keys. You can change this modifying the "Is part of Primary Key" checkbox on the "Edit properties of entity" screen on the "Fields mapped on database" subtab.

For example, if you have the entity "Customer" and you have the entity mapped on view "CustomerWhoBuyCandies" that is based on customer data, you could make the CustomerId of the "CustomerWhoBuyCandies" "part of the primary key", then you could make a 1:1 relation with the Customer entity.

thank you for the extensive information.

daelmo wrote:

I can think in a few differences (could be interpreted as advantages or disaventages depending the scenario):

well ... I understand the basic differences between tables and views. I'm conceptually still a bit confused about the fact that in LGP you can treat views as tables (entities) ...

daelmo wrote:

Entities usually have a primary key.

but now I can also add a primary key to a view. Which is quite an interesting aspect ...

daelmo wrote:

Views are read-only. You ussually want this.

well yes ... but if I now create an entity based on a view ... how does LGP behave if I want to insert something into this entity?

daelmo wrote:

Views are a direct way to obtain specific data. For example, in reporting scenarios, some people use them to obtain info directly from DB and bound them to report viewers.

that's what I was thinking about. Since I'm rather a db designer/analyst than programmer (unfortunately I have to do programming too), I would rather have the data retrieval logic in the db (view) than fetch/join it through LGP.

daelmo wrote:

For example, if you have the entity "Customer" and you have the entity mapped on view "CustomerWhoBuyCandies" that is based on customer data, you could make the CustomerId of the "CustomerWhoBuyCandies" "part of the primary key", then you could make a 1:1 relation with the Customer entity.

that's what I was referring to ... basically the entities in LGP can do everything that views can do (since they can be based on views) and many things more. So I'm wondering which advantage the views still have (in LGP) ...

If I base an entity on a view instead of using the view "directly", I can set primary key information, set relations (with this also use prefetch paths) etc.

Probably worth remembering that in a lot of (particularly large and data-sensitive) development scenarios, developers will have no access at all to the underlying tables, only to a set of views defined by a DBA. In this case it is very usefull to be able to do as much as possible with entities based on views...

Matt

MTrinder wrote:

Probably worth remembering that in a lot of (particularly large and data-sensitive) development scenarios, developers will have no access at all to the underlying tables, only to a set of views defined by a DBA. In this case it is very usefull to be able to do as much as possible with entities based on views...

I understand, but how does LGP behave if I try to "insert something into" an Entity that is based on a view?

Of course you can do tricks with triggers and then delegate the insert into the view to trigger inserts into the respective tables, but the standard behavior of an insert into a view (or an entity based on a view) will result in an exception, right?

If I'm correct, I'm wondering whether it wouldn't be helpful to set all fields of an entity that is based on a view to read-only by definition.

I think the concept always was mapping DB-Views as TypedViews. People then come with the necessity of the ability to relate this views with their entities.

For example, I use TypedViews for reporting purposes: just fetch the TypedView and bound it to my report viewer. Note: when the data must be filtered or the fields I want to include may vary (something 3 fields, someting 5) or when I want the ability to change the expression applied to a field dynamically at code, I use DynamicLists, but that is another conversation The point is, realting a "ResumeOfSales" view to some entity doesn't have sense for my scenario, for instance.

I use 'Views mapped as entities' when for example I have some views that have valuable read-only information and relating them to entities simplifies my code:

if (myCustomer.SalesOfThisMonthResume.Amount > 50000)
{
     myCustomer.IsPremium = true;   
}

myCustomer.Save();

daelmo wrote:

I think the concept always was mapping DB-Views as TypedViews. People then come with the necessity of the ability to relate this views with their entities.

For example, I use TypedViews for reporting purposes: just fetch the TypedView and bound it to my report viewer. Note: when the data must be filtered or the fields I want to include may vary (something 3 fields, someting 5) or when I want the ability to change the expression applied to a field dynamically at code, I use DynamicLists, but that is another conversation The point is, realting a "ResumeOfSales" view to some entity doesn't have sense for my scenario, for instance.

I use 'Views mapped as entities' when for example I have some views that have valuable read-only information and relating them to entities simplifies my code:

if (myCustomer.SalesOfThisMonthResume.Amount > 50000)
{
     myCustomer.IsPremium = true;   
}

myCustomer.Save();

Interesting idea to use related views to simplify coding this way. I will remember this for the future.

I guess there is no limitation to how many times a database object can be present in the LGP project. So I could theoretically import each view twice: Once as TypedView and once as an Entity. If I want to dynamically bind/join the view to other views/tables, I use the Entity. Otherwise I can just use the view ... does this make sense? It's a bit of an overhead, but since I can't have the best of both worlds in one LGP object ...

That's correct.

TypedView's are more or less something which was added in v1.0 5 years ago, as we didn't have mapping to views at that time. As this has been added a long time ago already, typedviews are nice for flat lists, but you can achieve the same thing by mapping an entity to the view and create a typedlist for that entity if you want the flat list for the view. So no need for importing them twice.