I'm having some trouble with Sqlite in memory. 

I have a class that has a CPF field - similar to US' SSN. As a bussiness rule, the CPF must be unique in the system.

So I've decided to make a check on the class that has this field. Now maybe there's code smell here: I check with the ORM if this is a Conflicting CPF. 

```cs
    private CPF cpf;
    public virtual CPF CPF
    {
        get { return cpf; }
        set
        {
            if (this.ormCreated) //Do not check if it is loaded from the DB. Otherwise, it loops, generating a StackOverflow exception
            {
                cpf = value;
            }
            else
            {
                this.setNewCpf(value);
            }
        }
    }

    private void setNewCpf(CPF newCpf)
    {
        if (this.cpf == newCpf)
        {
            return;
        }

        if (Helper.Orm.IsConflictingCpf(newCpf))
        {
            throw new ConflictingCpfException();
        }
        else
        {
            cpf = newCpf;
        }
    }

```

And here is the implementation, on the ORM Helper class.

```cs
    bool OrmHelper.IsConflictingCpf(CPF cpf)
    {
        int? cpfNumber = cpf.NumeroSemDV;
        if (cpfNumber.HasValue)
        {
            var teste = findByCpfNumber<Client>(cpf);
            return 
                (
                findByCpfNumber<Client>(cpf) != null ||
                findByCpfNumber<Adversary>(cpf) != null
                );
        }
        else
        {
            //CPFSemDV = Nullable 
            return false;
        }
    }

    private PersonType findByCpfNumber<PersonType> (CPF cpf) where PersonType : PessoaFisica
    {
        int? cpfNumber = cpf.NumeroSemDV;
        using (var session = this.NewSession())
        using (var transaction = session.BeginTransaction())
        {
            try
            {
                var person = session.Query<PersonType>()
                    .Where(c => c.CPF.NumeroSemDV == cpfNumber)
                    .FirstOrDefault<PersonType>();
                return person; 
            }
            catch (Exception) { transaction.Rollback(); }
            finally 
               { 
                  session.Close();
               }
        }
        return null;
    }

```

The problem happens in my tests. I'm using FluentNHibernate and In memory SQLite. 

```cs
    protected override FluentConfiguration PersistenceProvider 
    { 
        get 
        {
            return Fluently
                .Configure()
                .Database(
                    SQLiteConfiguration
                    .Standard
                    .InMemory()
                    .ShowSql()
                    );
        } 
    }

```

Here is the failing test.

```cs
    protected override void Given()
    {
        base.Given();
        var clients = new List<Client>();

        Client client1 = new Client("Luiz Angelo Heinzen")
        {
            Capaz = true,
            CPF = new CPF(18743509),
            eMail = "lah@furb.br"
        };

        session.Save(client1);
        session.Evict(client1);
    }

    [Then]
    public void Motherfaker()
    {
        Client fromDb;
        var clientsFromDb = session.Query<Client>()
            .Where(c => c.eMail == "lah@furb.br");
        fromDb = clientsFromDb.FirstOrDefault<Client>();

        Assert.AreEqual(fromDb.FullName, "Luiz Angelo Heinzen");

    }

```

The reason it fails? In the beginning it was failing because the table didn't exist. In memory sqlite destroys the schema on each new session. So I changed the code to return the same session on the NewSession(). But now it fails with a NHibernate exception: Session is closed. I've tested and if change the findByCpfNumber from this

```cs
    private PersonType findByCpfNumber<PersonType> (CPF cpf) where PersonType : PessoaFisica
    {
        int? cpfNumber = cpf.NumeroSemDV;
        using (var session = this.NewSession())
        using (var transaction = session.BeginTransaction())
        {
            try
            {
                var person = session.Query<PersonType>()
                    .Where(c => c.CPF.NumeroSemDV == cpfNumber)
                    .FirstOrDefault<PersonType>();
                return person; 
            }
            catch (Exception) { transaction.Rollback(); }
            finally 
               { 
                  session.Close();
               }
        }
        return null;
    }

```

to this

```cs
    private PersonType findByCpfNumber<PersonType> (CPF cpf) where PersonType : PessoaFisica
    {
        int? cpfNumber = cpf.NumeroSemDV;
        //using (var session = this.NewSession())
        var session = this.NewSession();
        using (var transaction = session.BeginTransaction())
        {
            try
            {
                var person = session.Query<PersonType>()
                    .Where(c => c.CPF.NumeroSemDV == cpfNumber)
                    .FirstOrDefault<PersonType>();
                return person; 
            }
            catch (Exception) { transaction.Rollback(); }
            finally 
               { 
                  //session.Close();
                  this.CloseSession(session);
               }
        }
        this.CloseSession(session);
        return null;
    }

```

the error doesn't happen anymore. Obviously, I'd have to implement the CloseSession method. It would close the Session on the Production database and it would do nothing if Sqlite is being used. 

But I'd rather configure SQLite in someway that it wouldn't dispose the session. I've read [here](https://stackoverflow.com/questions/189280/problem-using-sqlite-memory-with-nhibernate) about release\_mode, Pooling and Max Pool atributes. But I can't seem to find it in the FluentNHibernate so can't even test to see if it would work. I have the FluentNHibernate cloned and it seems to set the release\_mode set to on\_close, but that doesn't help. 

I've even tried:

```cs
    public override ISession NewSession()
    {
        if (this.session == null)
        {
            if (sessionFactory == null)
            {
                CreateSessionFactory();
            }
            this.session = sessionFactory.OpenSession();
        }
        if (!session.IsOpen)
        {
            sessionFactory.OpenSession(session.Connection);
            session.Connection.Open();
        }
        return session;
    }

```

But it keeps telling me that the Session is closed. So, anyone has any suggestions on how to approach this? 

Or does this so smelly that's beyond salvation?

I hope this is clear enough. And forgive my mistakes: I'm from Brazil and not a native english speaker. 

Thanks, 

Luiz Angelo.