SQL Like & In Operators | other errors
hi there
I am missing the SQL LIKE and IN operators. Are those currently not available or did I not find them?
I expected something like
var
q =
from tin something
where t.somecolumn like "blah%"
select t;
and
var q =
from tin something
where t.idcolumn in (myIDarray)
select t;
In the samples I saw a category"EXISTS/IN/ANY/ALL", but there were no IN and no EXISTS samples.I also have a problem with enums, but there's a work arround. If I have an field wich is of an enum type (int) and saved in the db as an int, dlinq can't load an instance of the class. Currently I convert the int to enum and back in the property accessors. Will this change?
Another thing, with associations.
privateEntityRef<ClassB> _ClassB;
[Association(Storage ="_ClassB", ThisKey ="ClassB")]
publicClassB ClassB {...}
If the value in the db is NULL then i get an empty sequence exception. I expected that the field would be set to null.
thank you!
pascal
[2448 byte] By [
pascalh] at [2008-2-4]
Pascal,
Thanks for trying out the DLinq preview. Here are some suggestions for translating SQL idioms into their LINQ counterparts:
1. "Like" functionality can often be covered using string.Contains() method.
2. Any() provides a way to handle common use of "exists" in SQL.
3. About the null reference - could you please share the query that results in an exception for the mapping specified. Here is one known issue but I can't tell if you are running into it or if it is something else until I see the query. The preview bits currently perform the equivalent of inner join rather than outer join. We are looking at expanding the functionality.
Thanks.
Dinesh Kulkarni
Program Manager - The LINQ Project
http://blogs.msdn.com/Dinesh.Kulkarni/
Hello
Thank you for the answers.
1. Well yes, simple querys can surely be done with the Contains method. That's ok.
2. Yes, you can use Any for exists. But what's with IN? There's no way to use Any for In, or didn't I get it.
3. Sure. I've made a smaller sample. Same effect. If you run the query with 1 as the ID everything is ok. If you run it with 2 as the ID you will get "Exception of type 'System.Query.EmptySequenceException' was thrown." The code follows.
Thank you.
| |
[Table(Name = "tblA")]
public class A{
int _AID;
[Column(Id = true, Storage = "_AID")]
public int AID { get { return _AID; } } private int? _BFK;
[Association(Storage = "_BFK")]
public int? BFK
{
get { return _BFK; }
} private EntityRef<B> _B;
[Association(Storage = "_B", ThisKey = "BFK")]
public B B
{
get { return this._B.Entity; }
set { this._B.Entity = value; }
}
} [Table(Name = "tblB")]
public class B{
int _BID;
[Column(Id = true, Storage = "_BID")]
public int BID { get { return _BID; } }
} class Program
{
static void Main(string[] args)
{
const string cnnstr = @"";
MyDB db = new MyDB(cnnstr);
var objs =
from c in db.GetTable<A>()
where c.AID == 2
select c;
A myA = objs.First();
}
}
|
== SQL ==
Create Database myDB
Go
Use myDB
GO
CREATE TABLE tblA (AID int NOT NULL ,BFK int NULL)
CREATE TABLE tblB (BID int NOT NULL)
ALTER TABLE tblA ADD CONSTRAINT PK_tblA PRIMARY KEY(AID)
ALTER TABLE tblB ADD CONSTRAINT PK_tblB PRIMARY KEY(BID)
ALTER TABLE tblA ADD CONSTRAINT FK_tblA_tblB FOREIGN KEY(BFK) REFERENCES tblB(BID)
insert tblB(BID) Values(1)
insert tblA(AID, BFK) Values(1, 1)
insert tblA(AID, BFK) Values(2, null)
== SQL ==
