c# - Linq query to select distinct record and count number of distinct record -

i have table tblvisitor stores visitors location details. has below structure.

vregion      nvarchar (100) null, vtimezone    nvarchar (100) null, vlat         nvarchar (max) null, //latitude vlong        nvarchar (max) null, //longitude 

now, storage depends on each session , when session expires same location details might stored again. trying display in map , getting latitude , longitude details in model class. model class below:

public class visitormapviewmodel {     public int count { get; set;}     public geocoordinate coords { get; set; } } 

am trying fill model below, no luck.

list<visitormapviewmodel> model = new list<visitormapviewmodel>(); var data = _db.tblvisitors.distinct().tolist().select(v => new visitormapviewmodel()             {                 coords = new system.device.location.geocoordinate(convert.todouble(v.vlat), convert.todouble(v.vlong)),                 count = _db.tblvisitors.groupby(m => new { m.vlat,m.vlong}).select(m=>m).distinct().count()             }).tolist(); 

there 2 problems here.. converting latitude , longitude values of type nvarchar double , getting distinct values of latitude , longitude , counting number of occurances.. above query keeps hitting 1 or other kind of exception when interchange converting tolist() before distinct() , vice versa.. feel near, not sure missing. on please.

you should 1 query groups on latitude , longitude , gets counts. convert results model. can use asenumerable when want switch sql queries in memory linq queries without needing overhead of creating list.

var data = (from visitor in _db.tblvisitors             group visitor new { visitor.vlat, visitor.vlong } grp             select new              {                 grp.key.vlat,                 grp.key.vlong,                 count = grp.count()             })     .asenumerable()     .select(x => new new visitormapviewmodel()         {             coords = new system.device.location.geocoordinate(                 double.parse(x.vlat),                  double.parse(x.vlong)),             count = x.count         })     .tolist(); 

alternatively in method syntax

var data = _db.tblvisitors     .groupby(v => new { v.vlat, v.vlong })     .select(grp => new          {             grp.key.vlat,             grp.key.vlong,             count = grp.count()         })     .asenumerable()     .select(x => new new visitormapviewmodel()         {             coords = new system.device.location.geocoordinate(                 double.parse(x.vlat),                  double.parse(x.vlong)),             count = x.count         })     .tolist();