查找类似地址的LINQ查询
我有一张桌子，是商店的地址。此表有多个地址组件字段，如地址号、街道名称、方向、后缀、前缀、城市、州和zip。(Edit：此地址表具有以前由用户添加的地址。我想他们来自同一个城镇、城市、州和国家。因此，我确实保留了城市、州、乡村和拉链，但没有用于查询。)
我的应用程序是从数据库中用户输入的地址中找到一个完全匹配的地址。如果没有完全匹配，则返回类似的地址。
用户输入的所有地址或存储在由Google标准化的数据库中，以避免不匹配，例如1234 N Johnson St、1234 North Johnson St或1234 North John Street。
这是我正在使用的精确匹配的查询。由于存储和输入的地址都是由标准化的，所以我得到了一个我想要的完全匹配的结果。
var exactMatch = (from address in db.Addresses
where address.PrimaryAddressNumber == userInput.Number && address.Directional == userInput.Direction && address.Suffix == userInput.Suffix && address.StreetName == userInput.StreetName
select new IncidentSite
{
FullAddress = 'address components goes here'
});然而，如果没有确切的匹配，那么我想给用户一个选项。据我所想，就是构建多个查询，然后组合在一起。它如我所料，但时间太长了。
我做的就像
private IQueryable<IncidentSite> GetSimilarAddress(UserInput userInput)
{
var numberDirectionStreetname = (from address in db.Addresses
where address.PrimaryAddressNumber == userInput.Number && address.Directional == userInput.Direction && address.StreetName == userInput.StreetName
select new IncidentSite
{
FullAddress = 'address components goes here'
});
var numberStreetname = (from address in db.Addresses
where address.PrimaryAddressNumber == userInput.Number && address.StreetName == userInput.StreetName
select new IncidentSite
{
FullAddress = 'address components goes here'
});
var streetname = (from address in db.Addresses
where address.StreetName == userInput.StreetName
select new IncidentSite
{
FullAddress = 'address components goes here'
});
var similarAddress = numberDirectionStreetname.Union(numberStreetname).Union(streetname);
return similarAddress;
}正如您在similarAdddress上看到的，它将从dbo.Addresses表中运行三个查询，但是使用不同的where语句，然后使用union来构建一个结果。
我相信我所做的并不是找到类似地址的更明智的方法。有什么好方法可以让我构建一个更简单、更高效的查询呢？
编辑：我想我还不清楚为什么我必须有三个不同的查询，而不是一个。原因是要向用户提供所有可能的结果。为了使它更详细的解释，请见下文。
如果用户搜索'1234北约翰逊街‘，如果没有准确的匹配返回，以下步骤执行。
首先，numberDirectionStreetname，选择所有地址匹配'1234北约翰逊‘。因此，结果可以是1234北约翰逊+大道/街/法院/道路/公园/等等。我希望它显示在列表的顶端，因为存在比下面的更多匹配的地址组件。
其次，numberStreetname，选择与‘1234Johnson’匹配的所有地址。因此，结果可以是1234 +南/北/东/西/etc+约翰逊+林荫大道/街道/法院/道路/公园路/等等。
第三，街道名称，选择与“Johnson”匹配的所有地址。因此，结果可以是9999 +南/北/东/西/etc+约翰逊+林荫大道/街道/法院/道路/公园路/等等。
如果可能的话，我想在一个查询中完成它。这也是我问题的一部分，不仅使它表现得更快，而且使它更简单。然而，它必须是三个单独的查询，您将如何订购它们？如果我的逻辑不理想，你会怎么建议？
回答 4
Stack Overflow用户
发布于 2017-02-03 02:44:14
别担心直接比较。因为您需要一个接近匹配的列表，所以只需要根据组件匹配的数量对结果进行排序。
下面是一个工作示例程序，如果一个地址的每个元素匹配，根据排名计算一个总体排名和顺序(排名越高，匹配越好)，则对其进行排序。
public class Program
{
private static readonly IEnumerable<Address> Addresses = new List<Address>
{
new Address{ Number = "1000", Direction = "North", Street = "Grand" },
new Address{ Number = "2000", Direction = "North", Street = "Broadway" },
new Address{ Number = "1000", Direction = "South", Street = "Main" },
new Address{ Number = "3000", Direction = "South", Street = "Grand" },
new Address{ Number = "2000", Direction = "East", Street = "Broadway" },
};
static void Main()
{
const string streetToMatch = "Broadway";
const string numberToMatch = "2000";
const string directionToMatch = "South";
var rankedAddresses = from address in Addresses
let streetRank = address.Street == streetToMatch ? 1 : 0
let numberRank = address.Number == numberToMatch ? 1 : 0
let directionRank = address.Direction == directionToMatch ? 1 : 0
let rank = streetRank + numberRank + directionRank
orderby rank descending
select new
{
Address = address,
Rank = rank
};
foreach (var rankedAddress in rankedAddresses)
{
var rank = rankedAddress.Rank;
var address = rankedAddress.Address;
Console.WriteLine($"Rank: {rank} | Address: {address.Number} {address.Direction} {address.Street}");
}
}
}
public class Address
{
public string Street { get; set; }
public string Number { get; set; }
public string Direction { get; set; }
}结果
军衔:2名地址:北百老汇2000 军衔:2名地址:东百老汇2000 军衔:1,000地址:南主1000 军衔:1名地址: 3000名南格兰德 军衔:0-地址: 1000北格兰德
Stack Overflow用户
发布于 2017-02-03 03:39:41
你所说的类似地址是什么意思？我想你所说的类似地址是指同一个州和同一个国家的类似地址？在这种情况下，您需要使用国家筛选出数据集，状态可能按国家的第一、第二、第三等顺序排列。您需要按此顺序缩小范围，以减少正在使用的行。这样做后，你可以用你的逻辑，找出类似的地址，按街道，号码等。即使在这里，我也建议采用自上而下的方法。
您的查询正在花费时间，这可能是由于查询必须处理的数据量。因此，过滤掉行是可行的方法。
此外，您可以避免发送muliptle查询和执行联合操作。您不能在一个查询中使用适当的或条件同时执行所有操作吗？
我的意思是这样的。使用Inersect的组合重写您的逻辑。
using System;
using System.Linq;
using System.Collections.Generic;
namespace mns
{
public class Program
{
private static readonly IEnumerable<Address> Addresses = new List<Address>
{
new Address{ Number = "1234", Direction = "South", Street = "Main" },
new Address{ Number = "1234", Direction = "North", Street = "Broadway" },
new Address{ Number = "1234", Direction = "North", Street = "Grand" },
new Address{ Number = "1234", Direction = "South", Street = "Broadway" },
new Address{ Number = "34", Direction = "East", Street = "Broadway" },
};
public static void Main()
{
const string streetToMatch = "Broadway";
const string numberToMatch = "1234";
const string directionToMatch = "South";
var combinedAdrress = numberToMatch +" "+ streetToMatch + " "+ directionToMatch;
var rankedAddresses = from address in Addresses.Where(s=>numberToMatch== s.Number).Intersect(Addresses.Where(s=>directionToMatch==s.Direction)).Intersect(Addresses.Where(s=>streetToMatch == s.Street))
.Union(Addresses.Where(s=>numberToMatch== s.Number).Intersect(Addresses.Where(s=>streetToMatch == s.Street)))
.Union(Addresses.Where(s=>streetToMatch == s.Street))
select new
{
Address = address.Number + " " + address.Street+ " "+ address.Direction
};
Console.WriteLine("You are searching for: "+combinedAdrress);;
foreach (var rankedAddress in rankedAddresses)
{
var address = rankedAddress.Address;
Console.WriteLine(address);
}
}
}
public class Address
{
public string Street { get; set; }
public string Number { get; set; }
public string Direction { get; set; }
}
}您可以更改输入值以测试我得到的.WHat
你在寻找:百老汇南1234号 1234百老汇南1234百老汇北34百老汇东
Stack Overflow用户
发布于 2017-02-03 00:25:34
为什么不先获取所有的streetNames，然后使用它作为您的主列表从那里过滤？
var streetname = (from address in db.Addresses
where address.StreetName == userInput.StreetName
select new IncidentSite
{
FullAddress = 'address components goes here'
});
var numberStreetname = (from address in streetname
where address.PrimaryAddressNumber == userInput.Number && address.StreetName == userInput.StreetName
select new IncidentSite
{
FullAddress = 'address components goes here'
});
var numberDirectionStreetname = (from address in numberStreetname
where address.PrimaryAddressNumber == userInput.Number && address.Directional == userInput.Direction && address.StreetName == userInput.StreetName
select new IncidentSite
{
FullAddress = 'address components goes here'
});https://stackoverflow.com/questions/42014678
复制相似问题
腾讯云开发者
