The funkiness that is LINQ.Contains

Ok, I'm running into brick walls.

I'm trying to do this:

from p in Products
join i in new[] {1,2,3}
on p.Id equals i
select p

I get

NotSupportedException 
Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

I try again with this:

from p in Products
where ( new[] {1,2,3}).Contains (p.Id )
select p

Woot got some data back in LinqPad.

Tried this in actual code...

IList<int> ids = new List<int>(new[] {1, 2, 3});
IQueryable<Product> product =
from p in Products
where ids.Contains(p.Id)
select p;

I get

Method 'Boolean Contains(Int32)' has no supported translation to SQL.

But it just worked in LinqPad...

Try again:

IEnumerable<int> ids = new int[] {1, 2, 3};
IQueryable<Product> product =
from p in Products
where ids.Contains(p.Id)
select p;

Bingo.

So summary:

In LINQ, IEnumerable.Contains is only implemented for the IEnumerable interface and not the IList interface.  Seems obvious now in hindsight.

Keeping the data in the database clean

On this new project we've designed a database from the ground up.  And we've done a lot of interesting design in the system, such as implementing one-to-one tables that mirror domain object inheritance in the actual system, and referencing a number of lookup tables.

There are no real data yet, so when we need to test something, it goes into the dev database pretty much via someone running an insert script.

Inevitably, we end up with bad data, data that doesn't match the design of the database, and don't show up on views that joins multiple tables.

In this area I've come to appreciate what my old company did.  They had heaps of views designed to catch bad data.

There's always a tiny part of my soul that says... but if you designed the database with proper constraints in the first place, you'd never have bad data in the first place.

May be our database constraints are too weak, but I suspect it's because we wanted the flexibility to define our data, and some of these flexible definitions can't be protected adequately with database constraints.

jliu

SQL Metal: No exclude table/view/sp functionality?

I like SQL Metal.  Anything that can save me time to write more app is good in my book.  I do find it extremely odd that I can't exclude tables/view/sp from it's DBML generation process.

After giving it a bit of thought, I figure I'll need to do a bit of work on #2

1. sqlmetal /database /dbml:my.dbml
2. run a clean utility to strip the dbml of tables/relationships that we're not interested in.
3. sqlmetal /code my.dbml

If I get this done anytime soon, I'll put it up on codeplex.

I still think perhaps in the next version of SQL Metal we'll see exclude flags anyway.

Food for thought: C# Extension Methods

Disclaimer, I have not tested any of the following code.

Write the following extension method:

public static int ToInt(this A a)
{
return Int32.Parse(a.ToString());
}

public class A
{
}

Now, say the class designer of A implemented it's own ToInt method.

public class A
{
public int ToInt(){ return 0; }
}

What happens now to the extension method?
My guess is that it would shadow the class method.

Now, assuming that the extension method shadows the class method, how could one access the class method in the extension method?

public static int ToInt(this A a)
{
return a.ToInt();  // infinite recursion?
}

Having different scope for your property

C# 2.0 gave us this:

private string _property;
public string Property
{
    get { return _property; }
    private set { _property = value; }
}

C# 3.0 gave us this:

public string Property
{
    get;
    private set;
}

Due to the rules of encapsulation, the accessor/getter usually has a more relaxed scope than the mutator/setter.

I've recently came across cases where the tables are turned.  Hurray for some dependency-injectioness!

[CreateNew]
IService MyService
{
   private get;
   internal set;
}