This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi, Do you have any demo about how to get started with Linq to MySQL database? Thanks!
|
|
|
Hi Udi, We do not have one specifically for MySQL but if you do the following: 1. Set your provider type on your LightSpeedContext to use the MySQL provider You should be able to undertake the standard getting started process as we show when using SQL Server. LightSpeed provides the abstraction from the specific database so if you use a LightSpeed LINQ query it will translate it down to run against the specified MySQL database. The getting started video can be watched here: http://www.mindscape.co.nz/products/lightspeed/screencasts/gettingstarted.aspx I hope that helps, John-Daniel Trask |
|
|
Hi John, I'm passed the connection problem :), thanks, I even managed to drag & drop some tables into the model. (i'm totally a newbie on this, so it's my first time...) now for the real question - is there a way to create the table entities at runtime? My DB schema may change from time to time, so i'd like to use linq somehow like this: Entity table = MyUnitOfWork["Products"]; var product = from table where ... select...; while creating all the entity tables when the application starts (windos service). Any link to that? Thanks, Udi
|
|
|
Regarding the dynamic entity name (MyUnitOfWork["Products"]): This is not really possible because LINQ deals in strong-typed entities. Consider: IQueryable<Entity> table = MyUnitOfWork["Products"]; // hypothetical e.??? could only use properties of the Entity base class. For example, where e.ProductName == "WonderProduct" would cause a compile failure because e is not known to be a Product. This is a LINQ feature not a LightSpeed limitation. You could work around it by casting: IQueryable<Product> table = MyUnitOfWork["Products"] as IQueryable<Product>; but this doesn't seem to offer any benefits over MyUnitOfWork.Products. However if you do really want this you can do it by adding an indexer to the MyUnitOfWork class via a partial class: partial class MyUnitOfWork { Regarding creating the entity tables: We don't have built-in support for this, but it is possible to do using migrations. You can either use the migrations tool to generate SQL scripts (and run them from your application), or can host the migrations yourself. However, we do not yet have documentation or samples for hosting migrations yourself, so it's probably not a great area to tackle until you've had a bit more experience with the product. So if you are always creating the tables I'd suggest generating the SQL scripts and running them using normal ADO.NET. (If you're having to alter existing tables and manage schema versions then it may be worth the extra effort of learning migrations.) |
|
|
Thanks Ivan! I really appreciate the help. I get your point, still I'd like to make it a little bit tougher for you if you don't mind :) - let's say that for the ‘where’ clause I’m only interested in one column - 'SequenceNumber' which all my tables define as a primary key. So the question is - Can I inherit from the Entity class a middleware class 'MyBaseEntity' that contains only the 'SequenceNumber' field, and from this class inherit all my other classes that represent all the tables, each containing the rest of the fields of the relevant table. now assuming I will be able to create my tables classes at runtime, I might be able to do something like this: class MyBaseEntity : Entity //compile time class { public long SequenceNumber{get…} // it’s readonly field – no updates will be made } class Products : MyBaseEntity // assuming this class is auto generated on startup { //the rest of ‘Product’ fields } IQueryable<MyBaseEntity> table = MyUnitOfWork["Products"]; // returns my auto generated ‘Products’ class Can this be done? Thanks, Udi
|
|
|
The primary key maps to Id so this is already defined in the base Entity<T> class -- you don't need to create your own derived class with a mapping for this (and it will cause errors if you try). But if you have a common non-PK field, say a CreatedBy field, then yes, you can define this on a base entity and have your other entities inherit from that base entity type. To do this in hand-coded entities just specify MyBaseEntity as your base class instead of Entity<T>. To do this in the designer create the base entity using the Toolbox, set it to Abstract, drag an Inheritance arrow from the derived entity to MyBaseEntity, and set the arrow's inheritance type to ConcreteTableInheritance. I think you will still run into problems with MyUnitOfWork["Products"] though. The problem is that IUnitOfWork.Query<Product> returns an IQueryable<Product>. Because C# 3 does not support generic covariance, this is NOT convertible to an IQueryable<MyBaseEntity> even though Product derives from MyBaseEntity. In addition, LINQ needs to be given the correct entity type at compile time so as to formulate the right query (i.e. to know to query over the Product table). So as far as I can see there is no way of getting away from referencing the Product type at compile time. You *can* do this stuff with the core IUnitOfWork API. Check out the Find(Query) method: you can set Query.EntityType to a type determined at runtime. You might also be able to do it with one of the dynamic LINQ frameworks out there -- I haven't investigated these. That said, I'm not sure what you're trying to achieve here. Creating dynamic classes, especially core business entity types like Product, is very unusual, because it makes it very hard to write code against them. Why not just define Product at compile time? |
|
|
Hi Ivan, Thanks again for the support!
The reason is that my whole database schema (~200 tables) is generated at runtime form some XML based files. This is because each table contains messages that are sent from a network device, and these messages tend to change every once in a while (BTW, each table may contain millions of messages, so performance is crucial too). Besides, creating 200 tables at compile time (and there are going to be lots more) seems not that convenient.
If I understand you correctly, you just implied it might be a good idea for me to move to C# 4.0 where covariance and contra variance are supported for containers? (I’ve been seeking for that killer argument for my boss, parallel extensions weren’t enough…)
Can you link me to some examples of the Find method you mentioned?
Again, I really appreciate the help! Udi
|
|
|
C# 4 might be a better fit for your requirements because of its support for covariance (which may help with your IQueryables) and dynamic binding (which may help with dispatching against runtime-created types). However your architecture sounds extremely specific and unusual so we can't really provide concrete guidance on that -- you'll need to investigate and prototype yourself to see if it helps. The Find method is documented in the LightSpeed help file. See Help Topics > LightSpeed > Querying - Basic Features, and the IUnitOfWork.Find method and Querying.Query and QueryExpression classes. You might also want to check out some of the dynamic LINQ frameworks -- Scott Guthrie posted about one a while back, and I think Scott Hanselman just did a post on another. I don't have links but Google should provide. |
|
|
Hi Ivan, Another idea came up to my mind – Lets say I can tolerate a “two phase” model in my app - i.e. prepare the entity model statically in a dedicated dll (MyEntityModel.dll), and then load this assembly at run time in my application, to get the correct and updated table classes. Remember that all I need to rely on in my LINQ queries is one or two fields that are common to all of the 160 classes. Is there a way to instruct the designer (or some other SDK) to create my entity model (all my tables) programmatically? (I thought about launching from my application a different process that imitates the user’s 'drag&drop tables to the lsmodel operation' through code, and then creates the 'MyEntityModel.dll')
That would help a lot I presume. Udi.
|
|
|
Yes, you can create the model from the database as part of your build process using the lsgen.exe command line tool. See the Help file for full info. lsgen.exe creates C# or VB files which you will then need to link into your project, or compile directly using csc.exe. |
|