CODEWITHSUNDEEP
nhibernateone-to-one
SexyMF
SexyMF

Reputation: 11165

Nhibernate generating too many queries for one to one mapping

When the application loads, I am charging all the Campaigns into the memory:

var all = session.Query<Campaign>().ToList();

Campaign has one service.

public CampaignMap()
{
  Table("`TR_Campaigns`");
  Schema("dbo");
  Not.LazyLoad();
  Id(x => .Id).GeneratedBy.Increment().Column("CampaignID");
  References(x => x.Service).Column("ServiceID").Not.LazyLoad();
  // I also tried hasone and it is bringing the same result
}

This is the service map:

public ServiceMap()
{
  Not.LazyLoad();
  Table("`TR_Services`");
  Id(x => x.Id, "ServiceID").GeneratedBy.Increment();
  Map(x => x.Name,"ServiceName");
}

The problem is that when I have 100 campaigns, and each campaign has different service, the mapping generates 100 queries to get each the service for the each campaign. If for example, ServiceID 2 repeats itself in more than one campaign, the query … WHERE service0_.ServiceID = 2 will be only run once (which is good).

This is the generated query:

SELECT service0_.ServiceID   as ServiceID4_0_,
       service0_.ServiceName as ServiceN2_4_0_
FROM   [TR_Services] service0_
WHERE  service0_.ServiceID = 8

Is that normal? What will I do when I have 1000 campaigns with 1000 services or even more…

Thanks

Update:
I see your edit and I think that it is work.
but CampaignMap has more relations, for example: 

 HasMany(x => x.LandingPageWeights).KeyColumn("CampaignID").Not.LazyLoad();

how do I handle it according to your query?
Doing LandingPageWeights = x.LandingPageWeights fails....
What is your suggestion about this?
Thanks

Upvotes: 0

Views: 1671

Answers (2)

SexyMF
SexyMF

Reputation: 11165

I think I found it. I have decreased to 2 queries:

var all = session.Query<Campaign>()
                            .Fetch(x => x.Service)
                            .Fetch(x => x.Supplier)
                            .ToList();

this one brings me the services in one query.

Thank you!!

Upvotes: 1

Sanja Melnichuk
Sanja Melnichuk

Reputation: 3505

You can avoid n+1 issue by reading this article

http://ayende.com/blog/1328/combating-the-select-n-1-problem-in-nhibernate

and this so answer

Prevent Fluent NHibernate select n+1

Edit

Select all data without n+1 issue

var items = session.Query<Campaign>().Select(x=> new Campaign
{
  Id = x.Id,
  Service = new Service
  {
     Id = x.Service.Id,
     Name = x.Service.Name
  }
}).ToList();

Upvotes: 1

Related Questions