in attempt write clean model class libraries, using ef5 code first. using entitytypeconfiguration various properties mapped columns of 2 views hosted on sql server 2005 server instance.
caution: know there bad table column naming going on, i'll have cope existing database. please forgive me.
after doing plumbing, can query client accounts...
var context = new data.entitycontext(); public iqueryable<clientaccount> getclients(list<string> usernameoremail) { return context.clientaccount.where(p => usernameoremail.contains(p.username) || usernameoremail.contains(p.email)).include("company").asqueryable(); } ...matching clients returned, including company details.
however, when trying obtain list of companies only, result contains loads of duplicates.
var companies = data.companies.where(c => c.isactive).orderby(c => c.name); using sql server profiler found that, reasons unclear me, engine comes left outer join in order include client's userid well:
select [extent1].[id] [id], [extent1].[company] [company], [extent1].[address] [address], [extent1].[addresssuffix] [addresssuffix], [extent1].[zip] [zip], [extent1].[city] [city], [extent1].[state] [state], [extent1].[countryname] [countryname], [extent1].[telephone] [telephone], [extent1].[fax] [fax], [extent1].[url] [url], [extent1].[latitude] [latitude], [extent1].[longitude] [longitude], [extent1].[ispublic] [ispublic], [extent1].[active] [active], [extent1].[parent] [parent], [extent2].[userid] [userid] [dbo].[view_companiescountrycontinent] [extent1] left outer join [dbo].[passportaccounts] [extent2] on ([extent2].[companyid] not null) , ([extent1].[id] = [extent2].[companyid]) 1 = [extent1].[active] i puzzled why, since have no link clients company model. view doesn't include user table , targets companies table.
it clear missing out on something, hope can me on track. below find details model, context , entitytypeconfigurations. thanks!
code info:
the model rather simple...
public class clientaccount : iuseraccount { public clientaccount() { } [key] public int clientid { get; set; } [displayname("user name")] public string username { get; set; } [displayname("first name")] public string firstname { get; set; } [displayname("last name")] public string lastname { get; set; } [displayname("job title")] public string jobtitle { get; set; } [displayname("company")] public virtual company company { get; set; } [displayname("direct phone")] public string phonedirect { get; set; } [displayname("mobile phone")] public string phonemobile { get; set; } [displayname("registration date")] public datetime dateregistered { get; set; } [displayname("last login")] public datetime lastlogin { get; set; } [emailaddress] [displayname("email")] public string email { get;set; } public bool isapproved { get; set; } public bool isactive { get; set; } public bool islockedout { { return !isactive; } } public bool isonline { { return (lastlogin.addminutes(10) > datetime.now); } } public datetime lastactivityat { get; set; } } public class company { [key] public int id { get; set; } public string name { get; set; } public string address { get; set; } public string address2 { get; set; } public string zip { get; set; } public string city { get; set; } public string state { get; set; } public string country { get; set; } public string telephone { get; set; } public string fax { get; set; } public string url { get; set; } public string timezone { get; set; } public coordinate coordinate { get; set; } public bool ispublic { get; set; } public bool isactive { get; set; } public int parentcompanyid { get; set; } } public class coordinate { public decimal? latitude { get; set; } public decimal? longitude { get; set; } } so far nothing special (right?). dbcontext:
public class entitycontext : dbcontext { public entitycontext() : base("name=entitycontext") { database.setinitializer<entitycontext>(null); } public entitycontext(string connectionstring) : base(connectionstring) { database.setinitializer<entitycontext>(null); } public dbset<clientaccount> clientaccount { get; set; } public dbset<company> companies { get; set; } protected override void onmodelcreating(dbmodelbuilder modelbuilder) { modelbuilder.configurations.add(new companyconfiguration()); modelbuilder.configurations.add(new clientaccountconfiguration()); base.onmodelcreating(modelbuilder); } } and entitytypeconfigurations...
public class clientaccountconfiguration : entitytypeconfiguration<clientaccount> { public clientaccountconfiguration() : base() { haskey(p => p.clientid); totable("clientaccounts"); // view clientaccounts property(p => p.clientid) .hascolumnname("userid") .hasdatabasegeneratedoption(databasegeneratedoption.identity) .isrequired(); property(p => p.email) .hascolumnname("useremail") .isrequired(); property(p => p.username) .hascolumnname("userlogin") .isrequired(); property(p => p.firstname).hascolumnname("userfirstname"); property(p => p.lastname).hascolumnname("userlastname"); property(p => p.phonedirect).hascolumnname("userphonedirect"); property(p => p.phonemobile).hascolumnname("userphonemobile"); property(p => p.jobtitle) .hascolumnname("userposition"); hasoptional(p => p.company) .withoptionaldependent() .map(p => p.mapkey("companyid")); property(p => p.isapproved).hascolumnname("employed"); property(p => p.isactive).hascolumnname("active"); property(p => p.lastactivityat).hascolumnname("updated"); property(p => p.lastlogin).hascolumnname("lastlogin").isoptional(); property(p => p.dateregistered).hascolumnname("created"); } } public class companyconfiguration : entitytypeconfiguration<company> { public companyconfiguration() { this.haskey(c => c.id); this.totable("view_companiescountrycontinent"); this.property(c => c.id) .hascolumnname("id") .hasdatabasegeneratedoption(databasegeneratedoption.identity); this.property(c => c.name) .hascolumnname("company") .isrequired(); this.property(c => c.address).hascolumnname("address"); this.property(c => c.address2).hascolumnname("addresssuffix"); this.property(c => c.country).hascolumnname("countryname"); this.property(c => c.city).hascolumnname("city"); this.property(c => c.zip).hascolumnname("zip"); this.property(c => c.state).hascolumnname("state"); this.property(c => c.coordinate.latitude).hascolumnname("latitude"); this.property(c => c.coordinate.longitude).hascolumnname("longitude"); this.property(c => c.timezone).hascolumnname("timezone"); this.property(c => c.ispublic).hascolumnname("ispublic"); this.property(c => c.isactive).hascolumnname("active"); this.property(c => c.parentcompanyid).hascolumnname("parent"); } }
following on solution provided aron.
after implementing fix, ran problem unable update company of clientaccount object. makes sense model did not allow companyid property of clientaccount object updated.
the foreign key property has included in model, secondly property name has match key property name of object points to. solved original issue. did require change (already existing) company model though, have effect on other projects use company class.
the changes:
in model added companyid property , changed name of company id property id companyid (in order match both property names):
public class clientaccount : iuseraccount { public clientaccount() { } ... public nullable<int> companyid { get; set; } [displayname("company")] public virtual company company { get; set; } ... } public class company { [key] public int companyid { get; set; } public string name { get; set; } ... } in entitytypeconfiguration mapped new companyid property corresponding database column name, , can leave out company mapping.
public class clientaccountconfiguration : entitytypeconfiguration<clientaccount> { public clientaccountconfiguration() : base() { ... property(p => p.companyid).hascolumnname("companyid"); /* hasoptional(p => p.company) .withoptionaldependent() .map(p => p.mapkey("companyid")); */ ... } } 
Comments
Post a Comment