Databases
StackOverflow2013
Postsdbo
POChanging IRepository to support IQueryable (LINQtoSQL queries)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POChanging IRepository to support IQueryable (LINQtoSQL queries)
    primarykey
    data
    text
    <p>I've inherited a system that uses the Castle Windsor IRepository pattern to abstract away from the DAL which is LinqToSQL.</p> <p>The main problem that I can see, is that IRepository only implements IEnumerable. So even the simplest of queries have to load ALL the data from the datatable, to return a single object.</p> <p>Current usage is as follows</p> <pre><code>using (IUnitOfWork context2 = IocServiceFactory.Resolve&lt;IUnitOfWork&gt;()) { KpiFormDocumentEntry entry = context2.GetRepository&lt;KpiFormDocumentEntry&gt;().FindById(id, KpiFormDocumentEntry.LoadOptions.FormItem); </code></pre> <p>And this uses lambda to filter, like so</p> <pre><code>public static KpiFormDocumentEntry FindById(this IRepository&lt;KpiFormDocumentEntry&gt; source, int id, KpiFormDocumentEntry.LoadOptions loadOptions) { return source.Where( qi =&gt; qi.Id == id ).LoadWith( loadOptions ).FirstOrDefault(); } </code></pre> <p>So it becomes a nice extension method.</p> <p><strong>My Question is</strong>, how can I use this same Interface/pattern etc. but also implement IQueryable to properly support LinqToSQL and get some serious performance improvements? </p> <p>The current implementation/Interfaces for IRepository are as follows</p> <pre><code>public interface IRepository&lt;T&gt; : IEnumerable&lt;T&gt; where T : class { void Add(T entity); void AddMany(IEnumerable&lt;T&gt; entities); void Delete(T entity); void DeleteMany(IEnumerable&lt;T&gt; entities); IEnumerable&lt;T&gt; All(); IEnumerable&lt;T&gt; Find(Func&lt;T, bool&gt; predicate); T FindFirst(Func&lt;T, bool&gt; predicate); } </code></pre> <p>and then this is implemented by an SqlClientRepository like so</p> <pre><code>public sealed class SqlClientRepository&lt;T&gt; : IRepository&lt;T&gt; where T : class { private readonly Table&lt;T&gt; _source; internal SqlClientRepository(Table&lt;T&gt; source) { if( source == null ) throw new ArgumentNullException( "source", Gratte.Aurora.SHlib.labelText("All_TableIsNull",1) ); _source = source; } //removed add delete etc public IEnumerable&lt;T&gt; All() { return _source; } public IEnumerator&lt;T&gt; GetEnumerator() { return _source.GetEnumerator(); } IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); } } </code></pre> <p><strong>The problem at the moment</strong> is, in our example above, the .Where is calling 'GetEnumerator', which then loads all rows into memory, and then looks for the one we need.</p> <p>If I change IRepository to implement IQueryable, I can't implement the three methods needed, as these are not public in the Table class.</p> <p>I think I should change the SQLClientRepository to be defined like so</p> <pre><code>public sealed class SqlClientRepository&lt;T&gt; : IQueryable&lt;T&gt;, IRepository&lt;T&gt; where T : class </code></pre> <p>And then implement the necessary methods, but I can't figure out how to pass the expressions around etc. as they are private members of the Table class, like so</p> <pre><code>public override Type ElementType { get { return _source.ElementType; } //Won't work as ElementType is private } public override Expression Expression { get { return _source.Expression; } //Won't work as Expression is private } public override IQueryProvider Provider { get { return _source.Provider; } //Won't work as Provider is private } </code></pre> <p>Any help really appreciated to move this from 'iterate through every row in the database after loading it' to 'select x where id=1'!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USadudley
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload