CODEWITHSUNDEEP
linq-to-sql
Hal
Hal

Reputation: 1264

Limiting results of System.Data.Linq.Table<T>

I am trying to inherit from my generated datacontext in LinqToSQL - something like this 

public class myContext : dbDataContext {
 public System.Data.Linq.Table<User>() Users {
  return (from x in base.Users() where x.DeletedOn.HasValue == false select x);
 }
}

But my Linq statement returns IQueryable which cannot cast to Table - does anyone know a way to limit the contents of a Linq.Table - I am trying to be certain that anywhere my Users table is accessed, it doesn't return those marked deleted. Perhaps I am going about this all wrong - any suggestions would be greatly appreciated.

Hal

Upvotes: 3

Views: 2449

Answers (8)

Hal
Hal

Reputation: 1264

I found the problem that I had with the relationships/associations not showing in the views. It seems that you have to go through each class in the dbml and set a primary key for views as it is unable to extract that information from the schema. I am in the process of setting the primary keys now and am planning to go the view route to isolate only non-deleted items.

Thanks and I will update more later.

Upvotes: 0

liammclennan
liammclennan

Reputation: 5368

Encapsulate your DataContext so that developers don't use Table in their queries. I have an 'All' property on my repositories that does a similar filtering to what you need. So then queries are like:

from item in All
where ...
select item

and all might be:

public IQueryable<T> All
{
    get { return MyDataContext.GetTable<T>.Where(entity => !entity.DeletedOn.HasValue); }
}

Upvotes: 1

Scott Nichols
Scott Nichols

Reputation: 6248

There are a couple of views we use in associations and they still appear just like any other relationship. We did need to add the associations manually. The only thing I can think to suggest is to take a look at the properties and decorated attributes generated for those classes and associations.

Add a couple tables that have the same relationship and compare those to the view that isn't showing up.

Also, sometimes the refresh on the server explorer connection doesn't seem to work correctly and the entities aren't created correctly initially, unless we remove them from the designer, close the project, then reopen the project and add them again from the server explorer. This is assuming you are using Visual Studio 2008 with the linq to sql .dbml designer.

Upvotes: 0

Scott Nichols
Scott Nichols

Reputation: 6248

Another approach would to be use views..

CREATE VIEW ActiveUsers as SELECT * FROM Users WHERE IsDeleted = 0

As far as linq to sql is concerned, that is just the same as a table. For any table that you needed the DeletedOn filtering, just create a view that uses the filter and use that in place of the table in your data context.

Upvotes: 2

Scott Nichols
Scott Nichols

Reputation: 6248

You could use discriminator column inheritance on the table, ie. a DeletedUsers table and ActiveUsers table where the discriminator column says which goes to which. Then in your code, just reference the Users.OfType ActiveUsers, which will never include anything deleted.

As a side note, how the heck do you do this with markdown?

Users.OfType<ActiveUsers>

I can get it in code, but not inline

Upvotes: 1

Hal
Hal

Reputation: 1264

Perhaps my comment to Keven sheffield's response may shed some light on what I am trying to accomplish:

I have a similar repository for most of my data access, but I am trying to be able to traverse my relationships and maintain the DeletedOn logic, without actually calling any additional methods. The objects are interrogated (spelling fixed) by a StringTemplate processor which can't call methods (just props/fields).

I will ultimately need this DeletedOn filtering for all of the tables in my application. The inherited class solution from Scott Nichols should work (although I will need to derive a class and relationships for around 30 tables - ouch), although I need to figure out how to check for a null value in my Derived Class Discriminator Value property.

I may just end up extended all my classes specifically for the StringTemplate processing, explicitly adding properties for the relationships I need, I would just love to be able to throw StringTemplate a [user] and have it walk through everything.

Upvotes: 0

Kevin Sheffield
Kevin Sheffield

Reputation: 3616

What I did in this circumstance is I created a repository class that passes back IQueryable but basically is just

from t in _db.Table
select t;

this is usually referenced by tableRepository.GetAllXXX(); but you could have a tableRepository.GetAllNonDeletedXXX(); that puts in that preliminary where clause to take out the deleted rows. This would allow you to get back the deleted ones, the undeleted ones and all rows using different methods.

Upvotes: 0

Mark Cidade
Mark Cidade

Reputation: 100017

You can use a stored procedure that returns all the mapped columns in the table for all the records that are not marked deleted, then map the LINQ to SQL class to the stored procedure's results. I think you just drag-drop the stored proc in Server Explorer on to the class in the LINQ to SQL designer.

Upvotes: 0

Related Questions