Querying Views - No ViewName or WithViewName

Hi, I'm attempting to use a view in my Model. I do not wish to have a backing table. However something is just not working right. Please see attached photo

Thanks.

Also, My views don't match the same scheme.

You are not specifying a view name in your query, Use the .ViewName() extension method with your LINQ query when working with that entity to indicate to LightSpeed that you want to query using the view rather than a backing table, without this present we assume there is a table in place.

e.g. as per http://www.mindscapehq.com/documentation/lightspeed/Advanced-Querying-Techniques/Exploring-the-Query-Object

Specify the ViewName property to run the query against a view instead of against the entity’s normal backing table. See Working with Database Views in the chapter Controlling the Database Mapping for details.

If you are using multiple views where the view doesnt always match the schema, you will need to mark fields which are not consistently present with a named aggregate (e.g. one for each differing view where the field is present) and then apply that aggregate accordingly based on the view being loaded from.

JEremy

Guess it helps to be using the Mindscape.LightSpeed.Linq namespace... :D
On a side note, what would be the best way to handle the following

DB1

• Database Tables
  
  1. Data
     
• View
  
  1. GetData ( returns data from Data table )

Main DB

• Stored Procedures
  1. sp_GetData
     Calls DB1.dbo.GetData

How would I implement Lightspeed to call MainDB.dbo.sp_GetData and return the view from DB1?
The # of databases will continue to grow, however I do not wish to modify my program's connection string each time.
So I'm try to use one connection string and expose stored procedures / functions in the Main DB in order to return data from the other tables.

Are you intending to modify data retrieved from a UnitOfWork pointing to the "Main DB" and have it update back against the Data table in "DB1"? If so you could do this with 2 UOW instances one for each database connection and then detach your entities loaded from the "Main DB" context and attach them against the one in "DB1" however this does seem like a bit of a leaky abstraction.

Instead if you are intending to gate access through stored procs you could look at leveraging CRUD procedures in general for that table (see: http://www.mindscapehq.com/documentation/lightspeed/Working-with-Legacy-Databases/CRUD-Stored-Procedures) which would allow you to strictly operate over "Main DB" and maintain a more appropriate access model.

This best approach will depend on why you have separated out your two databases and why you intent to load the data via the stored proc.Im guessing its more likely the second though?

Jeremy

Jeremy, Yes it is more like the second option you explained. This way I don't necessarily have to provide direct access to the created databases(these are not customer databases). I do not want to redo each connection string for every app I write so I wanted to code a shared dll repository that utilized stored procedures to return data, etc. My main problem right now is as mentioned before. I'm attempting to use a View without a backing table or for that matter any access to the tables at all. All the database user privs have are to execute the stored procedures that I expose with permissions. Can you please provide an example of what I need to do in order to call my View and return back a proper result set with the appropriate data? Remember that my view has a different schema than my tables, because of inner joins etc.

Thanks Jeremy!

Given you are wanting to work with these like a standard table I would look at handling by setting your access mode for the generated entity to be stored procedures and follow the details in http://www.mindscapehq.com/documentation/lightspeed/Working-with-Legacy-Databases/CRUD-Stored-Procedures for setting up the mapping for your procedures.

e.g.

LightSpeedContext points to the Main DB, your Data table is set to use Stored Procs as the access mode and you have CreateData, UpdateData, DeleteData and GetData procs mapped accordingly to serve for the CRUD operations. Internally GetData may be calling to the GetData view in DB1.

If you have a read only concern where you were using a seperate proc from normal you can handle this by making a query and defining the specific stored procedure name to execute. Details about invoking stored procedures are in http://www.mindscapehq.com/documentation/lightspeed/Controlling-the-Database-Mapping/Invoking-Stored-Procedures

e.g.

You have a GetDataDifferently view in DB1 which operates over Data. You set up a GetDataDifferently proc in Main DB and then you can call that by executing a ProcedureQuery with GetDataDifferently specified as the name and Data as the entity type.

Jeremy