How to get records in first table(projects) not present in second table(finances) with foreign key reference using nhibernate

1
votes

I'm trying to query on a simple data structure in nhibernate and MSSQL

dbo.Projects : Id(int, not null)

dbo.Finances : Id(int, not null), ProjectId(int,not null), foreign key references to dbo.projects

I want to get all the records in projects table that are not present in finances table where the finances table has a foreign key reference ProjectId. I am migrating to (Fluent) Nhibernate 3 from EntityFramework? 

    //So far I have got here: 
    public IQueryable<ProjectModel> GetProjectsNotPresentInFinance()
    {
        var factory = Fluently.Configure()
            .Database(MsSqlConfiguration
                .MsSql2008
                    .ConnectionString(m_connectionString))
            .Mappings(m => m.FluentMappings
                .AddFromAssemblyOf<ProjectMap>()                   
               ).BuildSessionFactory();

        using (var session = factory.OpenSession())
        {
            var allprojects = session.QueryOver<ProjectModel>();
            var projectsToReturn = allprojects.List<ProjectModel>().AsQueryable();

       //---    Something like : all the records not in finances table ---------
       // .Where( proj => !db.Finances.Where(fin => fin.ProjectId == proj.Id).Any())
       // .Select(project => new ProjectModel
       // {
       //     Id=project.Id,
       //     ProjectName = project.ProjectName,                 
       // });

            return projectsToReturn;
        }            

    }



public class FinanceModel
{
    public virtual int Id { get; set; }
    public virtual int ProjectId { get; set; }       
}

public class ProjectModel
{
    public virtual int Id { get; set; }
    public virtual string ProjectName { get; set; }
}

public class ProjectMap:ClassMap<ProjectModel>
{
    public ProjectMap() {
        Table("Projects");
        Id(x => x.Id);
        Map(x => x.ProjectName);
    }
}

public class FinanceMap : ClassMap<FinanceModel>
{
    public FinanceMap()
    {
        Table("Finances");
        Id(x => x.Id);
        References(x => x.ProjectModel);
    }
}

    //-------------------------------------------------------
    //This is an Equivalent working code Using EntityFramework :
    public IQueryable<ProjectModel> GetProjectsNotPresentInFinance() {
        IQueryable<ProjectModel> projectList = db.Projects
        .Where( proj => !db.Finances.Where(fin => fin.ProjectId == proj.Id).Any())
        .Select(project => new ProjectModel
        {
            Id=project.Id,
            ProjectName = project.ProjectName,                 
        });
        return projectList;
    }
    //-------------------------------------------------------
1
nhibernatefluent-nhibernate

1 Answers

1
votes

On second thought, you may try this without changing anything to your mapping, using a subquery :

var notOrphanProjectIdsSubquery = QueryOver.Of<FinanceModel>()
                                    .Select(x => x.ProjectId);

var orphanProjects =  session.QueryOver<ProjectModel>()
                         .WithSubquery
                         .WhereProperty(x=>x.Id)
                         .NotIn(notOrphanProjectIdsSubquery)
                         .List();

----------------------- Initial answer

Assuming you have a mapped Finances Property in your Project class, and according to https://stackoverflow.com/a/14980450/1236044, it should be something like :

var orphanProjects = session.QueryOver<ProjectModel>()
    .WhereRestrictionOn(x => x.Finances).IsEmpty()
    .List();

I must confess I am not proficient with FluentNH. I guess the classes and mappings should be something like this, hoping I'm not setting you on the wrong track... 

public class FinanceModel
{
    public virtual int Id { get; set; }
    public virtual int ProjectId { get; set; }       
    public virtual ProjectModel Project{get;set;}
}

public class ProjectModel
{
    public virtual int Id { get; set; }
    public virtual string ProjectName { get; set; }
    public virtual IList<FinanceModel> Finances { get; set; }
}


public class ProjectMap:ClassMap<ProjectModel>
{
    public ProjectMap() {
        Table("Projects");
        Id(x => x.Id);
        Map(x => x.ProjectName);
        HasMany(x => x.Finances);
    }
}

public class FinanceMap : ClassMap<FinanceModel>
{
    public FinanceMap()
    {
        Table("Finances");
        Id(x => x.Id);
        References(x => x.Project);
    }
}