在没有映射的情况下在NHibernate中连接表

I have the following two objects:

我有以下两个对象：

User

用户

class User {
    public int role;
}

Role

角色

class Role {
    public int id;
    public string name;
}

be note that role property inside User is int and not Role, that's our limitations.

请注意，User内的角色属性是int而不是Role，这是我们的限制。

I want to join between all the users and each of his role. In the mapping objects there is no reference as you can understand, just a simple type (int).

我想加入所有用户和他的每个角色。在映射对象中，没有可以理解的引用，只是一个简单的类型（int）。

How do I do that join statement?

我该怎么做加入声明？

2 个解决方案

#1

Yes, this "theta join" (as I just learned this term) is very handy and let's us not worry about putting in pointless mapping relationships.

是的，这个“theta join”（正如我刚刚学到的这个术语）非常方便，让我们不要担心放入毫无意义的映射关系。

WARNING HOWEVER IN USING THIS!!! This tripped me up a lot.

警告尽管如此使用!!!这让我大吃一惊。

Adding to the above example...

添加到上面的例子......

var list = new List<int>( { 2, 3 } ); // pretend in-memory data from something.
var a = 
   (from u in session.Query<User>()
    from x in list
    from r in session.Query<Role>()
    where u.role == r.id
    where r.id == x.id  // pretend list wants to limit to only certain roles.
    select new { u.Username, Role = r.name }).ToList();

THIS WILL BOMB with some NotSupported exception. The trick is that anything coming from NHibernate Session must come LAST. So this alteration WILL work:

这将使BOMB出现一些NotSupported异常。诀窍是来自NHibernate Session的任何东西都必须最后。所以这个改变将起作用：

var a = 
   (from x in list
    from u in session.Query<User>()
    from r in session.Query<Role>()
    where u.role == r.id
    where r.id == x.id  // pretend list wants to limit to only certain roles.
    select new { u.Username, Role = r.name }).ToList();

And and BTW, you can use join as well, however you have to make sure if you have any nullable data types, that you use the .Value if you are joining to something not-nullable.

而且，顺便说一下，你也可以使用join，但是你必须确保你是否有任何可以为空的数据类型，如果你加入了一个不可为空的东西你使用.Value。

var a = 
   (from x in list
    from u in session.Query<User>()
    join r in session.Query<Role>() on u.role equals r.id
    where r.id == x.id  // pretend list wants to limit to only certain roles.
    select new { u.Username, Role = r.name }).ToList();

And while we're at it, let's say you have a method that has some dynamic condition. In this example the 'list' which could be a list of roles to filter by, but don't filter at all if the list is not there. Well, if you do the .ToList() then you are causing this query to execute immediately. But instead you can add a condition and then execute it later:

虽然我们在这里，但是假设你有一种具有某种动态条件的方法。在此示例中，“list”可以是要过滤的角色列表，但如果列表不存在则根本不进行过滤。好吧，如果您执行.ToList（），那么您将导致此查询立即执行。但是你可以添加一个条件，然后再执行它：

var a = 
    from u in session.Query<User>()
    join r in session.Query<Role>() on u.role equals r.id
    where r.id == x.id  // pretend list wants to limit to only certain roles.
    select new { u.Username, Role = r.name, RoleID = r.id }; // Adding the Role ID into this output.

if (list != null) // assume if the list given is null, that means no filter.
{
    a = a.Where(x => list.Contains(x.RoleID));

    // WARNING. Unfortunately using the "theta" format here will not work. Not sure why.
}

var b = a.ToList(); // actually execute it.
var c = a.Select(x => new { x.Username, x.Role }).ToList() // if you insist on removing that extra RoleID in the output.

One last thing.. Sometimes some simple logic will fail when executed in the select new { .. } part. I don't have an explanation. In our case the logic was just converting a DB value of a uint to an Enumerator of a model. But to get around that, I just avoided doing that conversion while reading the data but saved the value. Then in a later step, after the data was loaded, I just did the conversion in another LINQ statement.

最后一件事......有些简单的逻辑在select new {..}部分执行时会失败。我没有解释。在我们的例子中，逻辑只是将uint的DB值转换为模型的Enumerator。但为了解决这个问题，我只是避免在读取数据的同时进行转换但保存了价值。然后在后面的步骤中，在加载数据之后，我只是在另一个LINQ语句中进行了转换。

DISCLAIMER: While I wrote many of these things all the past several weeks, I did not put this code into my compiler to verify 100%.

免责声明：虽然我在过去几周内写了很多这些东西，但我没有把这些代码放到我的编译器中来验证100％。

#2

It's called a theta join:

它被称为theta join：

var a = (from u in session.Query<User>()
        from r in session.Query<Role>()
        where u.role == r.id
        select new { u.Username, Role = r.name }).ToList();

Assuming you have a Username property on the User class.

假设您在User类上有Username属性。

#1