public void InsertAndSelect()
{
var varbitVal = "10011";
using (var context = new BloggingContext(ConnectionString))
{
var blog = new Blog()
{
Name = "Some blog name"
};
blog.Posts = new List <Post>();
for (int i = 0; i < 5; i++)
{
blog.Posts.Add(new Post()
{
Content = "Some post content " + i,
Rating = (byte)i,
Title = "Some post Title " + i,
VarbitColumn = varbitVal
});
}
context.Blogs.Add(blog);
context.NoColumnsEntities.Add(new NoColumnsEntity());
context.SaveChanges();
}
using (var context = new BloggingContext(ConnectionString))
{
var posts = from p in context.Posts
select p;
Assert.AreEqual(5, posts.Count());
foreach (var post in posts)
{
StringAssert.StartsWith("Some post Title ", post.Title);
Assert.AreEqual(varbitVal, post.VarbitColumn);
}
var someParameter = "Some";
Assert.IsTrue(context.Posts.Any(p => p.Title.StartsWith(someParameter)));
Assert.IsTrue(context.Posts.Select(p => p.VarbitColumn == varbitVal).First());
Assert.IsTrue(context.Posts.Select(p => p.VarbitColumn == "10011").First());
Assert.AreEqual(1, context.NoColumnsEntities.Count());
}
}
public void TsQueryNot()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
context.Blogs.Add(new Blog {
Name = "_"
});
context.SaveChanges();
var result = context.Blogs.Select(
x => NpgsqlTextFunctions.QueryNot(NpgsqlTextFunctions.AsTsQuery("cat"))).First();
Assert.That(
NpgsqlTsQuery.Parse(result).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse("! 'cat'").ToString()));
}
}
public void Test_string_type_inference_in_coalesce_statements()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
context.Blogs.Add(new Blog { Name = "Hello" });
context.SaveChanges();
string stringValue = "string_value";
var query = context.Blogs.Select(b => stringValue + "_postfix");
var blogTitle = query.First();
Assert.That(blogTitle, Is.EqualTo("string_value_postfix"));
Console.WriteLine(query.ToString());
StringAssert.AreEqualIgnoringCase(
"SELECT COALESCE(@p__linq__0,E'') || E'_postfix' AS \"C1\" FROM \"dbo\".\"Blogs\" AS \"Extent1\"",
query.ToString());
}
}
public void TsIsContained()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
context.Blogs.Add(new Blog {
Name = "_"
});
context.SaveChanges();
var result = context.Blogs.Select(
x =>
NpgsqlTextFunctions.QueryIsContained(
NpgsqlTextFunctions.AsTsQuery("cat"),
NpgsqlTextFunctions.AsTsQuery("cat & rat"))).First();
Assert.That(result, Is.True);
}
}
public void TsVectorConcat()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
context.Blogs.Add(new Blog {
Name = "_"
});
context.SaveChanges();
var result = context.Blogs.Select(
x => NpgsqlTextFunctions.AsTsVector("a:1 b:2")
+ NpgsqlTextFunctions.AsTsVector("c:1 d:2 b:3")).First();
Assert.That(
NpgsqlTsVector.Parse(result).ToString(),
Is.EqualTo(NpgsqlTsVector.Parse("'a':1 'b':2,5 'c':3 'd':4").ToString()));
}
}
public void NumNode()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog = new Blog
{
Name = "_"
};
context.Blogs.Add(blog);
context.SaveChanges();
var nodeCount = context
.Blogs
.Select(x => NpgsqlTextFunctions.NumNode(NpgsqlTextFunctions.ToTsQuery("(fat & rat) | cat")))
.FirstOrDefault();
Assert.That(nodeCount, Is.EqualTo(5));
}
}
public void Length()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog = new Blog
{
Name = "cooky cookie cookies piano pianos"
};
context.Blogs.Add(blog);
context.SaveChanges();
var lexemeCount = context
.Blogs
.Select(x => NpgsqlTextFunctions.Length(NpgsqlTextFunctions.ToTsVector(x.Name)))
.FirstOrDefault();
Assert.That(lexemeCount, Is.EqualTo(2));
}
}
public void Test_string_null_propagation()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
context.Blogs.Add(new Blog { Name = "Hello" });
context.SaveChanges();
string stringValue = "string_value";
var query = context.Blogs.Select(b => (stringValue ?? "default_value") + "_postfix");
var blog_title = query.First();
Assert.That(blog_title, Is.EqualTo("string_value_postfix"));
Console.WriteLine(query.ToString());
StringAssert.AreEqualIgnoringCase(
"SELECT CASE WHEN (COALESCE(@p__linq__0,E'default_value') IS NULL) THEN (E'')"
+ " WHEN (@p__linq__0 IS NULL) THEN (E'default_value') ELSE (@p__linq__0) END ||"
+ " E'_postfix' AS \"C1\" FROM \"dbo\".\"Blogs\" AS \"Extent1\"",
query.ToString());
}
}
public void SelectWithWhere_Ef_TruncateTime()
{
DateTime createdOnDate = new DateTime(2014, 05, 08);
using (var context = new BloggingContext(ConnectionString))
{
var blog = new Blog()
{
Name = "Some blog name"
};
blog.Posts = new List <Post>();
for (int i = 0; i < 5; i++)
{
blog.Posts.Add(new Post()
{
Content = "Some post content " + i,
Rating = (byte)i,
Title = "Some post Title " + i,
CreationDate = createdOnDate.AddHours(i)
});
}
context.Blogs.Add(blog);
context.SaveChanges();
}
using (var context = new BloggingContext(ConnectionString))
{
var posts = from p in context.Posts
let datePosted = DbFunctions.TruncateTime(p.CreationDate)
where p.Rating < 3 && datePosted == createdOnDate
select p;
Assert.AreEqual(3, posts.Count());
foreach (var post in posts)
{
Assert.Less(post.Rating, 3);
}
}
}
public void Test_non_composable_function()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
// Add some data and query it back using Stored Function
context.Blogs.Add(new Blog
{
Name = "Some blog1 name",
Posts = new List<Post>()
});
context.SaveChanges();
// Query back
var nameParameter = new ObjectParameter("Name", "blog1");
var blogs = ((IObjectContextAdapter)context).ObjectContext.ExecuteFunction<Blog>("GetBlogsByName2", nameParameter).ToArray();
Assert.AreEqual(1, blogs.Length);
Assert.AreEqual("Some blog1 name", blogs[0].Name);
}
}
public void ConversionToTsQuery()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
context.Blogs.Add(new Blog {
Name = "_"
});
context.SaveChanges();
const string expected = "'b' & 'c'";
var casted = context.Blogs.Select(x => NpgsqlTextFunctions.AsTsQuery(expected)).First();
Assert.That(
NpgsqlTsQuery.Parse(casted).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse(expected).ToString()));
var converted = context.Blogs.Select(x => NpgsqlTextFunctions.ToTsQuery("b & c")).First();
Assert.That(
NpgsqlTsQuery.Parse(converted).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse(expected).ToString()));
converted = context.Blogs.Select(x => NpgsqlTextFunctions.ToTsQuery("english", "b & c")).First();
Assert.That(
NpgsqlTsQuery.Parse(converted).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse(expected).ToString()));
converted = context.Blogs.Select(x => NpgsqlTextFunctions.PlainToTsQuery("b & c")).First();
Assert.That(
NpgsqlTsQuery.Parse(converted).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse(expected).ToString()));
converted = context.Blogs.Select(x => NpgsqlTextFunctions.PlainToTsQuery("english", "b & c")).First();
Assert.That(
NpgsqlTsQuery.Parse(converted).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse(expected).ToString()));
}
}
public void OrderBy()
{
using (var context = new BloggingContext(ConnectionString))
{
Random random = new Random();
var blog = new Blog()
{
Name = "Some blog name"
};
blog.Posts = new List <Post>();
for (int i = 0; i < 10; i++)
{
blog.Posts.Add(new Post()
{
Content = "Some post content " + i,
Rating = (byte)random.Next(0, 255),
Title = "Some post Title " + i
});
}
context.Blogs.Add(blog);
context.SaveChanges();
}
using (var context = new BloggingContext(ConnectionString))
{
var posts = from p in context.Posts
orderby p.Rating
select p;
Assert.AreEqual(10, posts.Count());
byte previousValue = 0;
foreach (var post in posts)
{
Assert.GreaterOrEqual(post.Rating, previousValue);
previousValue = post.Rating;
}
}
}
public void QueryTree()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog = new Blog
{
Name = "_"
};
context.Blogs.Add(blog);
context.SaveChanges();
var queryTree = context
.Blogs
.Select(x => NpgsqlTextFunctions.QueryTree(NpgsqlTextFunctions.ToTsQuery("foo & ! bar")))
.FirstOrDefault();
Assert.That(
NpgsqlTsQuery.Parse(queryTree).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse("'foo'").ToString()));
}
}
public void Strip()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog = new Blog
{
Name = "cooky cookie cookies piano pianos"
};
context.Blogs.Add(blog);
context.SaveChanges();
var strippedTsVector = context
.Blogs
.Select(x => NpgsqlTextFunctions.Strip(NpgsqlTextFunctions.ToTsVector(x.Name)))
.FirstOrDefault();
Assert.That(
NpgsqlTsVector.Parse(strippedTsVector).ToString(),
Is.EqualTo(NpgsqlTsVector.Parse("'cooki' 'piano'").ToString()));
}
}
public void Test_issue_27_select_ef_generated_literals_from_inner_select()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog = new Blog {
Name = "Hello"
};
context.Users.Add(new Administrator {
Blogs = new List <Blog> {
blog
}
});
context.Users.Add(new Editor());
context.SaveChanges();
var administrator = context.Users
.Where(x => x is Administrator) // Removing this changes the query to using a UNION which doesn't fail.
.Select(
x => new
{
// causes entity framework to emit a literal discriminator
Computed = x is Administrator
? "I administrate"
: x is Editor
? "I edit"
: "Unknown",
// causes an inner select to be emitted thus showing the issue
HasBlog = x.Blogs.Any()
})
.First();
Assert.That(administrator.Computed, Is.EqualTo("I administrate"));
Assert.That(administrator.HasBlog, Is.True);
}
}
public void TestTableValuedStoredFunctions()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
// Add some data and query it back using Stored Function
context.Blogs.Add(new Blog
{
Name = "Some blog1 name",
Posts = new List <Post>()
});
context.Blogs.Add(new Blog
{
Name = "Some blog2 name",
Posts = new List <Post>()
});
context.SaveChanges();
// Query back
var query = from b in context.GetBlogsByName("blog1")
select b;
var list = query.ToList();
Assert.AreEqual(1, list.Count);
Assert.AreEqual("Some blog1 name", list[0].Name);
// Query with projection
var query2 = from b in context.GetBlogsByName("blog1")
select new { b.Name, Something = 1 };
var list2 = query2.ToList();
Assert.AreEqual(1, list2.Count);
Assert.AreEqual("Some blog1 name", list2[0].Name);
Assert.AreEqual(1, list2[0].Something);
}
}
public void TsQueryPhrase()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog1 = new Blog
{
Name = "_"
};
context.Blogs.Add(blog1);
context.SaveChanges();
var newQuery = context
.Blogs.Select(x => NpgsqlTextFunctions.TsQueryPhrase("b", "c"))
.FirstOrDefault();
Assert.That(newQuery, Is.EqualTo("'b' <-> 'c'"));
newQuery = context
.Blogs.Select(x => NpgsqlTextFunctions.TsQueryPhrase("b", "c", 10))
.FirstOrDefault();
Assert.That(newQuery, Is.EqualTo("'b' <10> 'c'"));
}
}
public void MatchRegex(string pattern, string matchingInput, string mismatchingInput)
{
// Arrange
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
context.Blogs.Add(new Blog()
{
Name = matchingInput
});
context.Blogs.Add(new Blog()
{
Name = mismatchingInput
});
context.SaveChanges();
}
// Act
// Ensure correctness of a test case
var netMatchResult = Regex.IsMatch(matchingInput, pattern);
var netMismatchResult = Regex.IsMatch(mismatchingInput, pattern);
List <string> pgMatchResults;
List <string> pgMismatchResults;
List <string> pgMatchWithOptionsResults;
List <string> pgMismatchWithOptionsResults;
using (var context = new BloggingContext(ConnectionString))
{
pgMatchResults = (from b in context.Blogs
where NpgsqlTextFunctions.MatchRegex(b.Name, pattern)
select b.Name).ToList();
pgMismatchResults = (from b in context.Blogs
where !NpgsqlTextFunctions.MatchRegex(b.Name, pattern)
select b.Name).ToList();
pgMatchWithOptionsResults = (from b in context.Blogs
where NpgsqlTextFunctions.MatchRegex(b.Name, pattern, RegexOptions.None)
select b.Name).ToList();
pgMismatchWithOptionsResults = (from b in context.Blogs
where !NpgsqlTextFunctions.MatchRegex(b.Name, pattern, RegexOptions.None)
select b.Name).ToList();
}
// Assert
Assert.That(netMatchResult, Is.True);
Assert.That(netMismatchResult, Is.False);
Assert.That(pgMatchResults.Count, Is.EqualTo(1));
Assert.That(pgMatchResults[0], Is.EqualTo(matchingInput));
Assert.That(pgMismatchResults.Count, Is.EqualTo(1));
Assert.That(pgMismatchResults[0], Is.EqualTo(mismatchingInput));
Assert.That(pgMatchWithOptionsResults.Count, Is.EqualTo(1));
Assert.That(pgMatchWithOptionsResults[0], Is.EqualTo(matchingInput));
Assert.That(pgMismatchWithOptionsResults.Count, Is.EqualTo(1));
Assert.That(pgMismatchWithOptionsResults[0], Is.EqualTo(mismatchingInput));
}
public void CreateBloggingContext()
{
using (var db = new BloggingContext(new NpgsqlConnection(ConnectionStringEF)))
{
if (!(db.Database.Connection is NpgsqlConnection))
{
Assert.Fail(
"Connection type is \"" + db.Database.Connection.GetType().FullName + "\" should be \"" + typeof(NpgsqlConnection).FullName + "\"." + Environment.NewLine +
"Most likely wrong configuration in App.config file of Tests project.");
}
db.Database.Delete();
var blog = new Blog {
Name = "blogNameTest1"
};
db.Blogs.Add(blog);
db.SaveChanges();
var query = from b in db.Blogs
where b.Name == "blogNameTest1"
select b;
Assert.AreEqual(1, query.Count());
db.Database.Connection.Open();
using (var command = db.Database.Connection.CreateCommand())
{
command.CommandText = "select column_name, data_type, is_nullable, column_default from information_schema.columns where table_name = 'Blogs';";
List <string> expectedColumns = new List <string>(new string[] { "Name", "BlogId" });
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine((string)reader[0] + " " + (string)reader[1] + " " + (string)reader[2] + " " + (reader[3] ?? "").ToString());
switch ((string)reader[0])
{
case "Name":
expectedColumns.Remove((string)reader[0]);
Assert.AreEqual("text", (string)reader[1]);
Assert.AreEqual("YES", (string)reader[2]);
Assert.IsNullOrEmpty(reader[3] as string);
break;
case "BlogId":
expectedColumns.Remove((string)reader[0]);
Assert.AreEqual("integer", (string)reader[1]);
Assert.AreEqual("NO", (string)reader[2]);
Assert.AreEqual("nextval('dbo.\"Blogs_BlogId_seq\"'::regclass)", reader[3] as string);
break;
default:
Assert.Fail("Unknown column '" + (string)reader[0] + "' in Blogs table.");
break;
}
}
}
foreach (var columnName in expectedColumns)
{
Assert.Fail("Column '" + columnName + "' was not created in Blogs table.");
}
}
using (var command = db.Database.Connection.CreateCommand())
{
command.CommandText = "select column_name, data_type, is_nullable, column_default from information_schema.columns where table_name = 'Posts';";
List <string> expectedColumns = new List <string>(new string[] { "PostId", "Title", "Content", "BlogId" });
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine((string)reader[0] + " " + (string)reader[1] + " " + (string)reader[2] + " " + (reader[3] ?? "").ToString());
switch ((string)reader[0])
{
case "PostId":
expectedColumns.Remove((string)reader[0]);
Assert.AreEqual("integer", (string)reader[1]);
Assert.AreEqual("NO", (string)reader[2]);
Assert.AreEqual("nextval('dbo.\"Posts_PostId_seq\"'::regclass)", (string)reader[3]);
break;
case "Title":
expectedColumns.Remove((string)reader[0]);
Assert.AreEqual("text", (string)reader[1]);
Assert.AreEqual("YES", (string)reader[2]);
Assert.IsNullOrEmpty(reader[3] as string);
break;
case "Content":
expectedColumns.Remove((string)reader[0]);
Assert.AreEqual("text", (string)reader[1]);
Assert.AreEqual("YES", (string)reader[2]);
Assert.IsNullOrEmpty(reader[3] as string);
break;
case "BlogId":
expectedColumns.Remove((string)reader[0]);
Assert.AreEqual("integer", (string)reader[1]);
Assert.AreEqual("NO", (string)reader[2]);
Assert.AreEqual("0", (string)reader[3]);
break;
case "UniqueId":
expectedColumns.Remove((string)reader[0]);
Assert.AreEqual("uuid", (string)reader[1]);
Assert.AreEqual("NO", (string)reader[2]);
Assert.AreEqual("'00000000-0000-0000-0000-000000000000'::uuid", reader[3] as string);
//Assert.AreEqual("uuid_generate_v4()", reader[3] as string);
break;
case "Rating":
expectedColumns.Remove((string)reader[0]);
Assert.AreEqual("smallint", (string)reader[1]);
Assert.AreEqual("YES", (string)reader[2]);
Assert.IsNullOrEmpty(reader[3] as string);
break;
default:
Assert.Fail("Unknown column '" + (string)reader[0] + "' in Post table.");
break;
}
}
}
foreach (var columnName in expectedColumns)
{
Assert.Fail("Column '" + columnName + "' was not created in Posts table.");
}
}
}
}
