We'll release tomorrow a couple of templates which generate Linq to Sql code (classes and mapping file) from an LLBLGen Pro project. Code will be C#. It will generate a vs.net 2008 project file, a folder with per entity a class file, a datacontext file and a .dbml mappings file.

The main purpose is to offer people who need to use Linq to Sql a way to use the designer to refresh their meta-data. Linq to Sql's designer doesn't have a refresh feature, so updating a model is a pain with updated meta-data from the db. Our designer solves that

Don't worry, our own framework will stay our no.1. target, now and in the future, it's just an easy way to utilize our designer's power for other purposes as well .

Of course not all features of our designer are supported: no m:n relations, no inheritance with targetperentity entities, as Linq to Sql doesn't support these. Initially we also don't generate code for proc calls.

They're available in the customer area -> 2.5 section -> Tasks and templates

Hi Frans,

I think the contents of zip are not correct. After installing the latest build of llblgen pro and copying the files from the zip, I had to move the net3.5 folder in the AdditionalTemplates folder to a new folder call LinqToSql. And the new template group "Linq To Sql" only showed up after moving the file templatesGroup.config from the Tasks folder to the AdditionalTemplates folder.

After this the code was generated just fine (havent played with it yet).

Hmm, I think you're right. I'll update the archive. (files were moved a bit before deployment, which caused the problem)

(edit) updated

Hi Frans, this is actually great, as I came across a project in which they are mandating Ling to SQL, however one of the bummer things, is anytime I need to update the database using SqlMetal, I also have to go in an tweak some object names after the fact, and is a pain!

So this is a great addition, I would love to using a LLBLGen Project to generate the classes, much easier to maintain.

And as you guess, there is a question coming.... when do you think these templates will support stored procs? This client has a ton of them ( not that I am to happy with it )...

Thanks for all the great work Frans!

2 Things:

First Item:

There seems to be a bug in the dbml file... I have two tables:

Member MemberID (PK)

OrderItem OrderItemID (PK) BuyerID (FK) <- To MemberID SellerID (FK) <- To MemberID

I have named these in the Member Entity, OrderItemsAsBuyer and OrderItemsAsSeller respectively... and the DBML file has both items as OrderItemsAsBuyer ... It does indeed have the relationships in the DBML, it just seems to ignore the actual FieldMappedName.

EDIT: After more digging, I suspect this has something to do with the Association Name being identical. If you use SqlMetal to generate the dbml file, it uses an FK_OrderItem_Buyer and FK_OrderItem_Seller as the respective names, whereas the dbml file the templates output are simply Member_OrderItem for both.

2nd Item:

Is there a possibility to have a set of the templates which are more like the normal entity classes whereas the ClassNames actually have "Entity" attached ... So I would have MemberEntity rather than Member?

Thanks again Frans!

tzarger wrote:

Hi Frans, this is actually great, as I came across a project in which they are mandating Ling to SQL, however one of the bummer things, is anytime I need to update the database using SqlMetal, I also have to go in an tweak some object names after the fact, and is a pain!

It is indeed. I really don't understand why MS made such a lousy editor. The Entity framework editor isn't much better though. Their model refresher is also pretty dumb as in: it won't migrate your model to the next schema level: you've to do that manually. That can become a large job where tools could have taken the majority of work.

So this is a great addition, I would love to using a LLBLGen Project to generate the classes, much easier to maintain.

And as you guess, there is a question coming.... when do you think these templates will support stored procs? This client has a ton of them ( not that I am to happy with it )... Thanks for all the great work Frans!

There's a bit of a problem with procs and sqlserver. The Linq to sql designer uses a trick we did too in earlier versions of the sqlserver driver: it tries to determine the resultset of the procs. The downside is that this quite regularly leads to errors when the proc uses a temptable for example. It was so unreliable that we dropped it.

So their designer knows the resultset of most of the procs, and that allows them to map actions of entities onto procs. That's not possible in llblgen pro v2.x, so support for proc calls aren't added to these templates because of this.

tzarger wrote:

2 Things:

First Item:

There seems to be a bug in the dbml file... I have two tables:

Member MemberID (PK)

OrderItem OrderItemID (PK) BuyerID (FK) <- To MemberID SellerID (FK) <- To MemberID

I have named these in the Member Entity, OrderItemsAsBuyer and OrderItemsAsSeller respectively... and the DBML file has both items as OrderItemsAsBuyer ... It does indeed have the relationships in the DBML, it just seems to ignore the actual FieldMappedName.

EDIT: After more digging, I suspect this has something to do with the Association Name being identical. If you use SqlMetal to generate the dbml file, it uses an FK_OrderItem_Buyer and FK_OrderItem_Seller as the respective names, whereas the dbml file the templates output are simply Member_OrderItem for both.

Hmm, I did have a test schema with similar relations, will see if I can reproduce it again.

2nd Item: Is there a possibility to have a set of the templates which are more like the normal entity classes whereas the ClassNames actually have "Entity" attached ... So I would have MemberEntity rather than Member?

Thanks again Frans!

Why do you want the suffix exactly? Some people actually want to get rid of that suffix

Why do you want the suffix exactly? Some people actually want to get rid of that suffix

Because one of the things I enjoy about LLBLGen is I do not have to worry about naming collisions such as a table named "Application" with the .NET Application object. Having "Entity" on it prevents these types of issues.

Yeah, at first when I started using LLBLGen, I did not really care for the "Entity" on every class, but I learned reasons why it was actually a good thing, and figure you already thought throught that as you have so many things. Now it just seems "obvious" ... :-)

Okay, so maybe I like typing less code than the full explicit declaration, and I just need to get over that! :-)

Actually, I ran into a problem with this indeed during development I used northwind for some testing and it had Employee.Employee (for teh reference to the boss, I didn't rename the field), and it gave an error because the property wasn't allowed to have the same name as the type

I kept the names close to Linq to Sql's own naming scheme though, at least for this iteration.

I'll look into the issue you brought up with the names mapped on relations on monday.

Indeed, I now see what you mean:

<Association Name="Customer_Order" Member="Orders" OtherKey="CustomerId" Type="Order"/>
<Association Name="Addres_Customer" Member="VisitingAddress" ThisKey="VisitingAddressId" Type="Addres" IsForeignKey="true"/>
<Association Name="Addres_Customer" Member="BillingAddress" ThisKey="BillingAddressId" Type="Addres" IsForeignKey="true"/>

Twice the same name. Will fix it. (linq to sql simply appends a number to it btw)

Fixed in next build. .

(uploaded the new archive to the customer area).

First off, thanks for the update and fix!

Otis wrote:

There's a bit of a problem with procs and sqlserver. The Linq to sql designer uses a trick we did too in earlier versions of the sqlserver driver: it tries to determine the resultset of the procs. The downside is that this quite regularly leads to errors when the proc uses a temptable for example. It was so unreliable that we dropped it.

So their designer knows the resultset of most of the procs, and that allows them to map actions of entities onto procs. That's not possible in llblgen pro v2.x, so support for proc calls aren't added to these templates because of this.

Hi Frans, I have been thinking about this response, and ultimately, I think you are a fairly brilliant individual, and you have a great designer that is truly a great piece of software that I have in my toolbelt, and for when it comes down to using LinqToSql (when mandated by the customer), there are some things often times that are out of our control, such as Stored Procedures...

I was thinking, since you have Action and Retrieval Procedures set up in the Designer project, is it not possible to use that information to generate the stored procs on the LinqToSql templates? Let's say worse case scenario, you only script out Action Procedures, as for the most part, they perform a specific function which may be fairly complex that required a stored proc to begin with...

As far as the Retrieval Procs, perhaps you add a methodology to allow the developer to specify the return types or columns. That information could be optional, and explicitly used in the LinqToSql Templates.

I am just thinking that it might be a good market introduction to get the LinqToSql developers possibly to jump ship and join the LLBLGen family once they realize how great the designer is, and the power of it. But in order to do that, perhaps you might invest some effort on making a complete LinqToSql template output solution.

Of course, I think you do great work, and I appreciate your hard work as well. I am actually able to use the Templates as is on the project I mentioned I had, as they do not have stored procs here, but I know of another potential projects which is heavy on stored procs, and if I get that, I would love to use LLBLGen designer.

Any thoughts?

Sorry, another item... It seems if you use SqlMetal to generate the code and you tell it to pluralize, it pluralizes the Table Name, but keeps the Type Name singular.

I notice you mention in previous messages that you are trying to mimic the output of SqlMetal, or Linq Designer, and was curious if this could be implemented as well? [I will give up on the option to add Entity to the type name... :-)]

tzarger wrote:

First off, thanks for the update and fix!

Otis wrote:

There's a bit of a problem with procs and sqlserver. The Linq to sql designer uses a trick we did too in earlier versions of the sqlserver driver: it tries to determine the resultset of the procs. The downside is that this quite regularly leads to errors when the proc uses a temptable for example. It was so unreliable that we dropped it.

So their designer knows the resultset of most of the procs, and that allows them to map actions of entities onto procs. That's not possible in llblgen pro v2.x, so support for proc calls aren't added to these templates because of this.

Hi Frans, I have been thinking about this response, and ultimately, I think you are a fairly brilliant individual, and you have a great designer that is truly a great piece of software that I have in my toolbelt, and for when it comes down to using LinqToSql (when mandated by the customer), there are some things often times that are out of our control, such as Stored Procedures...

I was thinking, since you have Action and Retrieval Procedures set up in the Designer project, is it not possible to use that information to generate the stored procs on the LinqToSql templates? Let's say worse case scenario, you only script out Action Procedures, as for the most part, they perform a specific function which may be fairly complex that required a stored proc to begin with...

THanks for the compliments

I can emit code for the action procs, that's not a problem. the problem are the retrieval procs.

As far as the Retrieval Procs, perhaps you add a methodology to allow the developer to specify the return types or columns. That information could be optional, and explicitly used in the LinqToSql Templates.

I am just thinking that it might be a good market introduction to get the LinqToSql developers possibly to jump ship and join the LLBLGen family once they realize how great the designer is, and the power of it. But in order to do that, perhaps you might invest some effort on making a complete LinqToSql template output solution.

To effectively use this, the resultset layout of retrieval procs has to be retrieved again, and that's not a picknick (it's doable, but it's buggy). The additional thing to do is to create a layout editor where the user can specify the layout of the resultset.

We won't add that for v2.6, but v3 will get a new designer and we're thinking about adding this as well (as our editors will be rebuild so it's not that hard to add). But for the v2.x designers, mapping procs to entity activities isn't going to happen. That's indeed an area we currently can't handle.

tzarger wrote:

Sorry, another item... It seems if you use SqlMetal to generate the code and you tell it to pluralize, it pluralizes the Table Name, but keeps the Type Name singular. I notice you mention in previous messages that you are trying to mimic the output of SqlMetal, or Linq Designer, and was curious if this could be implemented as well? [I will give up on the option to add Entity to the type name... :-)]

I've never used sqlmetal

What I initially wanted to do was to get what the linq to sql designer spits out, so you can use the linq to sql code right out of the box. There are currently no 'options' possible, so the templates generate code as defined in the templates.

IMHO, plurification of entity names on the datacontext, as you describe, isn't a good idea. To get it, it is possible, the template has to call into the plurification/singularisation routine and assume the name used is singular. That's not always the case (as users can decide to use plural entity names).

Found another issue, if you have columns as NText and a few other datatypes, (if you need me to get them, I can send the exception) SqlMetal, I am assuming the Linq Designer would automatically put "UpdateCheck=UpdateCheck.Never" in, like this:

[Column(Storage="_FaqIssue", DbType="NText", UpdateCheck=UpdateCheck.Never)] public string FaqIssue { ... }

So, I would guess this is a bug...? Otherwise, I would need to hand tool that property, and would rather not.

UPDATE: Here is the SQL Error:

SQL Server does not handle comparison of NText, Text, Xml, or Image data types.

Ah, I did add some of these attributes to the templates, as that check surpression is placed in the output at a couple of locations, I guess I forgot one spot.

(it's a bit messy, sometimes you've to define it in the xml file, at another point in the attribute, or both.. )

I'll look into it.

Fixed it. It's in the newly uploaded linq to sql templates .zip

Otis wrote:

Fixed it. It's in the newly uploaded linq to sql templates .zip

Hi Frans, I downloaded the latest beta, and noticed the LinqToSql Templates are missing... I noticed the first beta had them, was that an oversight?

I think so, I didn't realize the templates were in the beta package. They're in the v2.5 templates section

Hi Frans, it seems if you hide any fields mapped on relations, when generating the LinqToSql templates, it outputs them regardless if they are hidden or not... Is this something that can obey which are hidden?

tzarger wrote:

Hi Frans, it seems if you hide any fields mapped on relations, when generating the LinqToSql templates, it outputs them regardless if they are hidden or not... Is this something that can obey which are hidden?

Yes, they ignore hidden fields/relations for now, the thing is that I didn't look too deep into what to do with a relation which for example isn't known at the FK side, should the PK side be there as well? THe thing is that it looks like linq to sql uses info from both sides, so if any side is hidden, no side can be used, hence there's no code which checks for hidden parts as that won't work anyway (to my knowledge of linq to sql)